风流倜傥三金老师

分类

/ 注册

全部文章（共53篇）

题解 | 统计员工薪资扣除比例

SELECT a.staff_id, a.staff_name, CONCAT( ROUND(b.dock_salary/normal_salary * 100.0,1) ,'%') AS dock_ratio FROM staff_tb AS...

2025-11-02

0 18

题解 | 短视频直播间晚上11-12点之间各直播间的在线人数

SELECT b.room_id,b.room_name, COUNT(DISTINCT a.user_id) AS user_count FROM user_view_tb AS a INNER JOIN room_info_tb AS b ON (a.room_id = ...

2025-11-01

0 19

题解 | 查询成绩

WITH t1 AS ( SELECT sId FROM SC WHERE cId IN (1,2,3) GROUP BY sId HAVING (SUM(score) / 3) > 60) SELECT COUNT(*) AS 'count(*)' FROM t1;

2025-11-01

0 20

题解 | 查询成绩

WITH t1 AS ( SELECT sId FROM SC WHERE cId IN (1,2,3) GROUP BY sId HAVING (SUM(score) / 3) > 60) -- 没有用AVG是因为，我认为缺考应该算作0分，比如（80+80+0）/3 而非AVG（8...

2025-11-01

0 23

题解 | 请写出计算粉丝ctr的sql语句

SELECT ROUND( SUM(c.read_num) / SUM(c.show_num) ,4) AS fans_ctr FROM a INNER JOIN b ON (a.author_id = b.author_id) INNER JOIN ...

2025-11-01

0 17

题解 | 统计快递从创建订单到发出间隔时长

SELECT ROUND( AVG(TIMESTAMPDIFF(MINUTE,a.create_time,b.out_time)/60) ,3) AS time FROM express_tb AS a INNER JOIN exp_action_tb AS b ...

2025-10-31

0 21

题解 | 统计各等级会员用户下订单总额

SELECT b.vip, COALESCE(SUM(a.order_price),0) AS order_total FROM order_tb AS a RIGHT JOIN uservip_tb AS b ON (a.user_id = b.user_id) GROUP...

2025-10-31

0 19

题解 | 统计商家不同会员每日访问人次及访问人数

SELECT b.vip, COUNT(*) AS visit_nums, COUNT(DISTINCT a.user_id) AS visit_users FROM visit_tb AS a INNER JOIN uservip_tb AS b ON (a.use...

2025-10-31

0 25

题解 | 统计用户从访问到下单的转化率

WITH t1 AS ( -- 计算每日下单人数 SELECT COUNT(DISTINCT user_id) AS dds, DATE(order_time) AS ddt FROM order_tb GROUP BY DATE(or...

2025-10-31

1 28

题解 | 查询下订单用户访问次数？

SELECT a.user_id, COUNT(DISTINCT a.info_id) AS visit_nums -- 我感觉这个去重才是陷阱 FROM visit_tb AS a INNER JOIN order_tb AS b ON (a.user_id = b...

2025-10-31

0 18