12.13sql知识总结

1.

请你写一个SQL查找积分最高的用户的id，名字，以及他的总积分是多少(可能有多个)，查询结果按照id升序排序

select u.id,u.name,a.grade from
(select g.user_id,g.grade,rank() over (order by g.grade desc) r_number from
(select user_id,sum(case type when 'add' then grade_num else grade_num*(-1) end)
 as grade
from grade_info group by user_id 
 )g
 group by user_id
 )a
 join user as u
 on a.user_id=u.id
 where a.r_number=1
 order by u.id 

本题相较前面多了减分的记录，因此需要case when来匹配计算。

2.

请你写出SQL语句查询在2025年投递简历的每个岗位，每一个月内收到简历的数目，和对应的2026年的同一个月同岗位，收到简历的数目，最后的结果先按first_year_mon月份降序，再按job降序排序显示

select h1.job,first_year_mon,first_year_cnt,second_year_mon,second_year_cnt from 
(select job,date_format(date,'%Y-%m') as first_year_mon,sum(num) as first_year_cnt
from resume_info where date like "2025%" group by job,first_year_mon)h1
inner join 
(select job,date_format(date,'%Y-%m')as second_year_mon,sum(num) as second_year_cnt
from resume_info where date like '2026%' group by job,second_year_mon)h2
on h1.job=h2.job and right(first_year_mon,2) =right(second_year_mon,2)
order by first_year_mon DESC,h1.job DESC;

使用date_format后数据变成字符串，不能使用month()函数获取月份，因此变成字符串处理，使用like “%” 进行最左优先匹配模糊查询，使用right获取月份信息并作为联表条件。

3.

请你写出一个sql语句查询在2025-10-15以后，如果有一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程，那么输出这个用户的user_id，以及满足前面条件的第一次购买成功的C++课程或Java课程或Python课程的日期first_buy_date，以及满足前面条件的第二次购买成功的C++课程或Java课程或Python课程的日期second_buy_date，以及购买成功的C++课程或Java课程或Python课程的次数cnt，并且输出结果按照user_id升序排序

select a.user_id,min(date) as first_buy_date,
max(date) as second_buy_date,a.cnt from 
(select user_id,date,row_number() over (partition by user_id order by date) as r_number,
count(id) over (partition by user_id) cnt
from order_info 
where date > '2025-10-15'
and product_name in ('C++','Java','Python')
and status = 'completed')a
where a.r_number<=2 and a.cnt>=2
group by user_id,a.cnt
order by a.user_id

子表查出用户登录情况统计信息，外表筛选留下满足条件信息，最后用聚合函数min(),max()获取第一次购买的时间和第二次购买的时间。