SELECT vi.tag, CONCAT(ROUND(AVG(IF((TIMESTAMPDIFF(second, uv.start_time, uv.end_time) / vi.duration) >= 1, 1, TIMESTAMPDIFF(second, uv.start_time, uv.end_time) / vi.duration)) * 100, 2), '%') AS avg_play_progress FROM tb_user_video_log AS uv LEFT OUTER JOIN tb_video_info AS vi ON uv.video_id = vi...

SELECT uv.video_id, ROUND(SUM(IF((uv.end_time - uv.start_time) >= vi.duration, 1, 0)) / COUNT(uv.video_id), 3) AS avg_comp_play_rate FROM tb_user_video_log AS uv LEFT OUTER JOIN tb_video_info AS vi ON uv.video_id = vi.video_id WHERE YEAR(uv.start_time) = 2021 GROUP BY uv.video_id ORDER BY avg_com...

SELECT e.emp_no, s.salary, e.last_name, e.first_name FROM employees AS e LEFT OUTER JOIN salaries AS s ON s.emp_no = e.emp_no WHERE s.salary = (SELECT MAX(salary) FROM salaries #找出第二高工资 WHERE salary < (SELECT MAX(salary) FROM salaries)) #找出最高工资 小...

SELECT s.emp_no, s.salary FROM (SELECT m.emp_no, DENSE_RANK() OVER(ORDER BY salary DESC) AS rn, m.salary FROM salaries AS m) AS s WHERE s.rn = 2; 使用DENSE_RANK()OVER(ORDER BY )窗口函数，适用于有多个员工同时薪水排第二的情况。

SELECT t.title, AVG(s.salary) FROM salaries AS s LEFT OUTER JOIN titles AS t on s.emp_no = t.emp_no WHERE t.title IS NOT NULL GROUP BY t.title; 要注意有的员工可能没有职称，此时可以调换表联结的顺序，也可以先筛选再分组再聚合。

SELECT * FROM employees WHERE emp_no % 2 = 1 AND last_name != 'Mary' ORDER BY hire_date DESC; 是奇数即除以2取余数为1，% 2 表示除以2之后取余。

同一天可能存在多名职工入职，使用窗口函数，不能用ROW_NUMBER()，要用DENSE_RANK()

SELECT * FROM employees WHERE hire_date = (SELECT MAX(hire_date) FROM employees);

SELECT up.device_id, up.university, COUNT(qd.question_id) AS question_cnt, SUM(IF(qd.result = 'right', 1, 0)) AS right_question_cnt FROM user_profile AS up LEFT OUTER JOIN question_practice_detail AS qd ON up.device_id = qd.device_id AND MONTH(qd.date) = 8 WHERE up.university = '复旦大学' GROUP BY up...

SELECT z.device_id, z.university, z.gpa FROM ( SELECT device_id, university, gpa, ROW_NUMBER() OVER(PARTITION BY university ORDER BY gpa) AS ra FROM user_profile) AS z WHERE z.ra = 1; 窗口函数 ROW_NUMBER() OVER(PARTITION BY university ORDER BY gpa)的排名结果不能直接出现在WHERE子句中，需要将窗口函数结果作为子查询（单独的新表...

SELECT DAY(q.date) AS day, COUNT(q.device_id) AS question_cnt FROM question_practice_detail AS q WHERE MONTH(q.date) = 8 AND YEAR(q.date) = 2021 GROUP BY q.date; 属于分类聚合查询，故需要加入分组语句。

SELECT u.device_id, u.gender, CASE WHEN age < 20 THEN '20岁以下' WHEN age BETWEEN 20 AND 24 THEN '20-24岁' WHEN age >= 25 THEN '25岁及以上' WHEN age is NULL THEN '其他' END AS age_cut FROM user_profile AS u; 多条件函数CASE的使用

SELECT IF(IFNULL(age, 0) < 25, '25岁以下', '25岁及以上') AS age_cut, COUNT(*) AS number FROM user_profile GROUP BY age_cut; 条件函数IF的使用

SELECT z.* FROM (SELECT up.university, qd.difficult_level, COUNT(qp.question_id) / COUNT(DISTINCT qp.device_id) FROM question_practice_detail AS qp LEFT OUTER JOIN question_detail AS qd ON qp.question_id = qd.question_id LEFT OUTER JOIN user_profile AS up ON qp.device_id = up.device_id GROUP BY up...