风流倜傥三金老师

分类

/ 注册

全部文章（共53篇）

题解 | 未下单用户统计

SELECT COUNT(*) AS 未下单人数 FROM order_log AS a RIGHT JOIN user_info AS b ON a.uid = b.uid WHERE a.uid IS NULL;

2025-11-10

0 24

题解 | 返回顾客名称和相关订单号以及每个订单的总价

SELECT khb.cust_name, ddb.order_num, SUM(spb.item_price * spb.quantity) AS OrderTotal FROM Orders AS ddb INNER JOIN Customers AS khb O...

2025-11-10

0 25

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

/* 需要输出每个视频的同时播放量，并且排序可以用UNION自连接，开始时间标记为1，结束时间标记为-1 */ WITH -- CTE开启 t1 AS ( -- 清洗得出用户行为表底表 SELECT cid, start_time AS sj, -- 行...

2025-11-10

0 26

题解 | 统计2021年10月每个退货率不大于0.5的商品各项指标

/* 先算出退货率小于0.5的商品然后以同样的计算方法计算出其他几个指标 */ WITH -- 使用CTE更加直观 t1 AS ( -- 先计算出退货率不大于0.5的商品，用作自连接过滤 SELECT product_id FROM tb_u...

2025-11-06

0 38

题解 | 支付间隔平均值

SELECT CAST( AVG( ABS(TIMESTAMPDIFF(SECOND,a.time,b.time)) ) AS SIGNED ) AS gap FROM o...

2025-11-04

0 23

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

WITH t1 AS ( SELECT DATE(a.log_time) AS log_day, a.user_id, RANK() OVER (PARTITION BY DATE(a.log_time) ORDER BY a.log_time) AS rk, b.h...

2025-11-04

0 20

题解 | 统计各个部门平均薪资

SELECT b.department, AVG(a.normal_salary - a.dock_salary) AS avg_salary FROM salary_tb AS a INNER JOIN staff_tb AS b ON (a.staff_id = b.st...

2025-11-04

0 24

题解 | 查询单日多次下订单的用户信息？

SELECT DATE(a.order_time) AS order_date, a.user_id, COUNT(*) AS order_nums, b.vip FROM order_tb AS a INNER JOIN uservip_tb AS b ON...

2025-11-04

0 22

题解 | 更新用户积分信息？

/*用SUM CASE WHEN*/ WITH t1 AS ( -- 计算出近日的新增积分 SELECT user_id, SUM(CASE WHEN order_price > 100 THEN order_price ELSE 0 END) AS point ...

2025-11-02

0 24

题解 | 统计用户获得积分

SELECT user_id, SUM(FLOOR(TIMESTAMPDIFF(SECOND,visit_time,leave_time) / 60 / 10)) AS 'point' FROM visit_tb AS a GROUP BY user_id ORDER BY poin...

2025-11-02

0 20