牛客479560235号

分类

/ 注册

全部文章（共75篇）

题解 | 各个部门实际平均薪资和男女员工实际平均薪资

With t_salary AS( SELECT t1.department, ROUND(AVG(t2.normal_salary - t2.dock_salary),2) avg_salary FROM staff_tb t1 INNER JOI...

2025-04-17

0 40

题解 | 统计每个产品的销售情况

with max_sales as ( SELECT product_id max_product_id, DATE_format (order_date, '%Y%m') M_order_date, S...

2025-04-17

0 47

题解 | 商品价格排名

SELECT a.product_id,a.product_name,a.type,a.price FROM( SELECT *,DENSE_RANK() OVER(partition by type order by price DESC) ranking FROM product...

2025-04-14

0 54

题解 | 统计快递运输时长

SELECT t1.exp_type,ROUND(AVG(TIMESTAMPDIFF(second,out_time,in_time)/3600),1) time FROM express_tb t1 RIGHT JOIN exp_action_tb t2 ON t1.exp_number = t2...

2025-04-11

0 40

题解 | 完成员工考核试卷突出的非领导员工

with avg_exam AS ( SELECT exam_id,AVG(score) avg_score,AVG(timestampdiff(second,start_time,submit_time)) avg_time FROM exam_record GROUP ...

2025-04-10

0 38

题解 | 淘宝店铺的实际销售额与客单价

SELECT ROUND(SUM(t1.n_sales*t2.goods_price),3) sales_total, ROUND(SUM(t1.n_sales*t2.goods_price)/AVG(t1.n_users),3) per_trans FROM( SELECT goods_...

2025-04-10

0 46

题解 | 被重复观看次数最多的3个视频

SELECT t1.cid,t1.pv,ROW_NUMBER() OVER(ORDER BY t1.pv DESC,t2.release_date DESC) FROM ( SELECT b.cid, SUM(b.c_cnt) pv FROM ( ...

2025-04-09

0 81

题解 | 每天登陆最早的用户的内容喜好

SELECT a.date log_day,a.user_id,b.hobby FROM ( SELECT t1.user_id,t1.date FROM ( SELECT DATE(log_time) date,user_id,DENSE_RANK() OVER(p...

2025-04-07

0 60

题解 | 统计加班员工占比

SELECT t2.department,CONCAT(ROUND((COUNT(t1.staff_id)/COUNT(t2.staff_id))*100,1),'%') ratio FROM( SELECT staff_id,timestampdiff(second,first_clock...

2025-04-07

0 47

题解 | 每个商品的销售总额

SELECT t4.name product_name, t3.total_sales, t3.category_rank FROM ( SELECT t1.product_id, SUM(t2.quantity) total_sales, DENSE_RANK(...

2025-03-25

1 60