SQL86 实习广场投递简历分析(三)
select a.job, first_year_mon, first_year_cnt, second_year_mon, second_year_cntfrom(select job, date_format(date, '%Y-%m') as first_year_mon, sum(num) as first_year_cntfrom resume_infowhere YEAR(date) = '2025'group by job, first_year_monorder by first_year_mon desc, job desc) ainner join (select job, date_format(date, '%Y-%m') as second_year_mon, sum(num) as second_year_cntfrom resume_infowhere YEAR(date) = '2026'group by job, second_year_monorder by second_year_mon desc, job desc) bon a.job = b.job and substring(first_year_mon,5,7) = substring(second_year_mon,5,7)order by first_year_mon desc, job desc
解题思路：substring：截取字符串
SQL87 最差是第几名(一)
select grade, sum(number) over(order by grade)from class_grade
select d.grade, sum(c.number)from class_grade cleft join class_grade d on c.grade <= d.gradegroup by d.gradeorder by d.grade
SQL88 最差是第几名(二)
select gradefrom(select grade, (select sum(number) from class_grade) as total, sum(number) over(order by grade) as a,sum(number) over(order by grade desc) as bfrom class_grade) cwhere a >= total / 2 and b >= total / 2order by grade
解题思路：当某一数的正序和逆序累计均大于整个序列的数字个数的一半即为中位数
1,2,3,4 : 正序：1，3，6，10 逆序：10，9，7，4
SQL89 获得积分最多的人(一)
select u.name, sum(grade_num) as num from grade_info g left join user u on g.user_id = u.id group by u.name order by num desc limit 1
SQL90 获得积分最多的人(二)
select a.user_id, u.name, a.grade_sum from ( select user_id, sum(grade_num) as grade_sum, dense_rank() over(order by sum(grade_num) desc) as `rank` from grade_info group by user_id ) a left join user u on u.id = a.user_id where `rank` = 1
解题思路：利用窗口函数对总数进行排序
