白加黑__

分类

/ 注册

全部文章（共49篇）

题解 | 最受欢迎的top3课程

SELECT prt.cid, COUNT(DISTINCT prt.id) pv, SUM(TIMESTAMPDIFF(MINUTE, prt.start_time, prt.end_time)) time_len FROM play_record_tb prt JOIN ...

2025-05-18

0 46

题解 | 输出播放量最高的视频（妙用累加函数）

SELECT cid, MAX(uv) max_peak_uv FROM (SELECT cid, SUM(mark)OVER(PARTITION BY cid ORDER BY time) uv FROM (SEL...

2025-05-18

0 78

题解 | 输出播放量最高的视频

SELECT DISTINCT cid, MAX(uv) max_peak_uv FROM (SELECT t1.cid, SUM(if(t1.start_time BETWEEN t2.start_time AND t2.end_time...

2025-05-18

0 65

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

WITH t0 AS( SELECT product_id, MAX(month_sales) max_monthly_quantity FROM (SELECT product_id, SUBSTRING(order_date,1,7) m...

2025-05-15

3 635

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

WITH t1 AS ( SELECT customer_id, MAX(order_date) latest_order_date FROM orders GROUP BY customer_id ) SELECT o.cu...

2025-04-02

4 81

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

SELECT st.department, ROUND(SUM(normal_salary - dock_salary)/COUNT(st.staff_id),2) average_actual_salary, ROUND(IFNULL(AVG(IF(st.staff_gen...

2025-04-02

0 61

题解 | 统计每个产品的销售情况（没有那么复杂吧老铁）

WITH t1 AS( SELECT DISTINCT p.product_id, SUM(o.quantity * unit_price) total_sales, p.unit_price, SUM(o.quantity) total_quantity, ...

2025-04-01

8 115

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

WITH t1 AS( SELECT o.customer_id, c.customer_name, MAX(o.order_date) date1 FROM orders o JOIN customers c ON c.cus...

2025-04-01

0 54

题解 | 商品id数据清洗统计

SELECT SUBSTRING_INDEX(order_id,'_',-1) product_id, COUNT(DISTINCT SUBSTRING_INDEX(order_id,'_',1)) cnt FROM order_log GROUP BY SUBSTRING_INDE...

2025-04-01

0 66

题解 | 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

SELECT e.emp_no, salary, last_name , first_name FROM employees e JOIN salaries s ON e.emp_no = s.emp_no WHERE salary IN (SELECT ...

2025-03-07

0 74