WITH t1 AS ( SELECT user_id, FLOOR(SUM(TIMESTAMPDIFF(MINUTE , visit_time,leave_time)) / 10) AS point FROM visit_tb GROUP BY user_id ) SELECT * FROM t1 ORDER BY point DESC,user_id ASC; # 这道题的关键在于FLoor(A/B)这个整除函数的使用！

with t1 AS ( SELECT a.staff_id, b.staff_name, ROUND( 100 * a.dock_salary / a.normal_salary ,1) AS dock_ratio FROM salary_tb a LEFT JOIN staff_tb b ON a.staff_id = b.staff_id WHERE b.department = 'dep1' ORDER BY dock_ratio DESC ) SELECT staff_id, staff_name, CONCAT(dock_ratio,'%') AS dock_ratio FROM ...

WITH t1 AS ( SELECT user_id, DATE(order_time) AS order_date FROM order_tb GROUP BY user_id, DATE(order_time) ), t2 AS ( SELECT user_id, DATE(visit_time) AS visit_date FROM visit_tb ), t3 AS ( SELECT order_date, COUNT(DISTINCT user_id) AS buy_num FROM t1 GROUP BY order_date ), t4 AS ( SELECT visit_da...

WITH t1 AS ( SELECT * FROM order_tb WHERE DATE(order_time) = '2022-09-02' ), t2 AS ( SELECT * FROM visit_tb WHERE DATE(visit_time)='2022-09-02' AND user_id IN (SELECT user_id FROM t1 GROUP BY user_id) ), t3 AS ( SELECT user_id, count(user_id) AS visit_nums FROM t2 GROUP BY user_id ORDER BY visit_num...

WITH t1 AS ( SELECT b.vip, a.order_price FROM order_tb a LEFT JOIN uservip_tb b ON a.user_id = b.user_id ), t2 AS ( SELECT vip FROM uservip_tb GROUP BY vip ), t3 AS ( SELECT vip, SUM(order_price) AS order_total FROM t1 GROUP BY vip ), t4 AS ( SELECT t2.vip, IFNULL(t3.order_total,0) AS order_total FR...

WITH t1 AS ( SELECT a.user_id, b.vip FROM visit_tb a LEFT JOIN uservip_tb b ON a.user_id = b.user_id ), t2 AS ( SELECT vip, count(user_id) AS visit_nums, count(DISTINCT user_id) AS visit_users FROM t1 GROUP BY vip ORDER BY visit_nums DESC )SELECT * FROM t2; # 这道题关键在于是否去重计数！

WITH t1 AS ( SELECT SC.sId, SC.cid, score FROM SC LEFT JOIN Course c ON SC.cid = c.cid WHERE c.cname in ('语文','数学','英语') ), t2 AS ( SELECT sid, AVG(score) AS avg_score FROM t1 GROUP BY sid HAVING avg_score > 60 )SELECT count(*) AS `count(*)` FROM t2; # 这道题关键在于表名要归一化！

WITH t1 AS ( SELECT c.content_id, c.fans_id, c.read_num, c.show_num, b.author_id FROM c LEFT JOIN b ON c.content_id = b.content_id ), t2 AS ( SELECT t1.author_id, t1.fans_id, t1.content_id, t1.read_num, t1.show_num, a.create_date FROM t1 LEFT JOIN a ON (t1.author_id,t1.fans_id) = (a.author_id,a.fans...

WITH t1 AS ( SELECT cid, uid, count(cid) AS pv FROM play_record_tb GROUP BY uid, cid HAVING pv >= 2 ), t2 AS ( SELECT t1.cid, t1.pv, ROW_NUMBER() over (ORDER BY t1.pv DESC, release_date DESC) AS rk FROM t1 LEFT JOIN course_info_tb a ON t1.cid = a.cid )SELECT cid, CONCAT(pv,'.000') AS pv, rk FROM ...

WITH t1 AS ( SELECT * FROM user_view_tb WHERE HOUR(out_time) = 23 OR (HOUR(out_time) = 24 AND MINUTE(out_time) = 0 AND SECOND(out_time) = 0) ), t2 AS ( SELECT room_id, count(DISTINCT user_id) AS user_count FROM t1 GROUP BY room_id ORDER BY user_count DESC ), t3 AS ( SELECT t2.room_id, b.room_name, u...

WITH t1 AS ( SELECT a.video_id, TIMESTAMPDIFF(SECOND,start_time,end_time) AS time_delta, b.duration FROM tb_user_video_log a LEFT JOIN tb_video_info b ON a.video_id = b.video_id WHERE SUBSTR(start_time,1,4) = '2021' HAVING time_delta >= duration ), t2 AS ( SELECT video_id, COUNT(video_id) AS wanb...

WITH t1 AS ( SELECT user_id, DATE(log_time) AS log_date FROM login_tb WHERE user_id in (SELECT user_id FROM register_tb) GROUP BY user_id, DATE(log_time) ), t2 AS ( SELECT user_id, log_date, RANK() over (PARTITION BY user_id ORDER BY log_date) AS ranking FROM t1 ), t3 AS ( SELECT user_id, date_add(l...

WITH t1 AS ( SELECT staff_id, TIMESTAMPDIFF(SECOND ,first_clockin,last_clockin) / 3600 AS work_hours FROM attendent_tb ), t2 AS ( SELECT post, ROUND(AVG(work_hours),3) AS work_hours FROM t1 LEFT JOIN staff_tb st ON t1.staff_id = st.staff_id GROUP BY post ORDER BY work_hours DESC )SELECT * FROM t2; #...

WITH t1 AS ( SELECT a.quantity, p.name, p.category FROM orders a LEFT JOIN products p on a.product_id = p.product_id ), t2 AS ( SELECT name, SUM(quantity) AS total_sales FROM t1 GROUP BY name ), t3 AS ( SELECT t2.name AS product_name, t2.total_sales, RANK() over (PARTITION BY p.category ORDER BY t2....