白色森林

分类

/ 注册

全部文章（共49篇）

题解 | 目标月份的品类销售简报

WITH category_stats AS ( SELECT p.category, COUNT(DISTINCT oi.order_id) AS orders_cnt, COUNT(DISTINCT o.buyer_id) AS buyer...

2025-11-10

0 6

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

SELECT e.employee_id, e.employee_name, pf.performance_score AS first_half_2024_score, ROW_NUMBER() OVER (PARTITION BY pj.project_id OR...

2025-11-03

0 10

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

#计算平均分和平均时间 WITH avg_exam AS ( SELECT ei.tag, AVG(TIMESTAMPDIFF(SECOND, er.start_time, er.submit_time)) AS avg_exam_time, ...

2025-10-31

0 11

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

#计算每个用户对每个视频的观看次数，并且筛选出观看次数大于1的 WITH play AS ( SELECT uid, cid, COUNT(*) as play_count FROM play_record_tb ...

2025-10-30

0 9

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

WITH song_pv_ranked AS ( SELECT MONTH(p.fdate) AS month, ROW_NUMBER() OVER (PARTITION BY MONTH(p.fdate) ORDER BY COUNT(*) DESC,s.s...

2025-10-28

0 14

题解 | 更新员工信息表

#先找出更新表中的日期排序 WITH UPDATE_DT_RANKED AS (SELECT EMPLOYEE_ID, UPDATE_DT, NEW_POSITION, ROW_NUMBER() OVER (PARTITION BY EMPLOYEE_ID ORDE...

2025-10-28

0 16

题解 | 每个顾客购买的最新产品名称

SELECT o.customer_id, c.customer_name, p.product_name AS latest_order FROM orders AS o INNER JOIN customers AS c ON o.customer...

2025-10-07

0 15

题解 | 查询订单

SELECT o.order_id, c.customer_name, o.order_date FROM orders AS o INNER JOIN customers AS c ON o.customer_id=c.customer_id WHE...

2025-10-07

0 19

题解 | 每个客户的账户总金额

SELECT customer_id, SUM(balance) AS sum_balance FROM account GROUP BY customer_id ORDER BY sum_balance DESC, customer_id;

2025-10-06

0 22

题解 | 统计快递运输时长

SELECT e.exp_type, ROUND(AVG(TIMESTAMPDIFF(SECOND,a.out_time,a.in_time))/3600,1) AS time FROM express_tb AS e INNER JOIN exp_actio...

2025-10-06

0 20