风流倜傥三金老师

分类

/ 注册

全部文章（共53篇）

题解 | 统计加班员工占比

WITH t1 AS ( SELECT staff_id, TIMESTAMPDIFF(SECOND,first_clockin,last_clockin) / 3600 AS sj FROM attendent_tb ) SELECT b.department, ...

2026-01-18

0 8

题解 | 下单复盘

/*分两步，先计算订单明细相关金额数量等，然后把结果左连接给客户表，否则GROUP BY 会报错*/WITHt1 AS (SELECT a.order_id, a.customer_id, a.order_date, b.sku, b.qty * b.price AS ...

2026-01-05

0 22

题解 | 每个顾客最近一次下单的订单信息

/* 这题使用GROUP BY + MAX同样可以达成，但是效率与可读性不如窗口函数，这是窗口函数的基础应用 */ WITH t1 AS ( -- 使用窗口函数对每位客户的订单以倒序排名 SELECT o.order_id, c.customer_id, c.cus...

2025-12-28

0 28

题解 | 网易云音乐推荐(网易校招笔试真题)

SELECT c.music_name FROM follow AS a INNER JOIN music_likes AS b ON a.follower_id = b.user_id INNER JOIN music AS c ON b.music_id = c.id WHERE a.u...

2025-12-20

0 33

题解 | 查询订单

WITH t1 AS ( SELECT a.order_id, b.customer_name, a.order_date, ROW_NUMBER() OVER(PARTITION BY b.customer_name ORDER BY a.order_date ...

2025-12-16

0 28

题解 | 每个部门薪资排名前两名员工

WITH t1 AS ( SELECT *, RANK() OVER(PARTITION BY department ORDER BY salary DESC) AS rk FROM employees ) SELECT department,employee_name,...

2025-12-16

0 27

题解 | 贷款情况

/* ①关联4个表，展示每一个贷款的相关信息 ②以城市和贷款类型分组求出每个城市最常申请的贷款类型名称 ③以城市分组求出其他指标 ④关联两个表求出结果 */ WITH t1 AS ( -- 关联4个表，展示每一个贷款的相关信息 SELECT kehu.customer_id, ...

2025-11-30

0 22

题解 | 最长连续登录天数

WITH t1 AS ( -- 清洗数据，得出每个人都在哪些天登陆过 SELECT DISTINCT fdate,user_id FROM tb_dau WHERE fdate >= '2023-01-01' AND fdate < '2023-02-01' ), t2...

2025-11-22

0 32

题解 | 每个月Top3的周杰伦歌曲

WITH t1 AS ( SELECT -- 清洗数据，获取2022年18~25岁用户每个月每首周杰伦歌曲的播放次数 MONTH(a.fdate) AS month, b.song_id, b.song_name, COUNT(*) AS play_pv F...

2025-11-22

0 35

题解 | 分析每个员工在不同项目中的绩效情况

WITH t1 AS ( -- 关联两表清洗数据，并计算出每个员工上半年的绩效总分 SELECT yg.employee_id, yg.employee_name, yg.department, xm.project_name AS project_group...

2025-11-18

0 29