牛客SQL 全部题目 SQL278-SQL288 11套代码及解析（8）

SQL278 实习广场投递简历分析(一)

select
job,
sum(num) as cnt 
from resume_info
where date between '2025-1-1' and '2025-12-31'
group by job
order by cnt desc

SQL279 实习广场投递简历分析(二)

select
job,
date_format(date,'%Y-%m'),
sum(num) as cnt
from resume_info
where year(date)='2025'
group by date_format(date,'%Y-%m'),job
order by date_format(date,'%Y-%m') desc,cnt desc

SQL280 实习广场投递简历分析(三)

1. SUBSTRING(name,5,3) 截取name这个字段 从第五个字符开始 只截取之后的3个字符 例子：SELECT
   SUBSTRING(“成都融资事业部”,5,3) 结果：事业部

select
a.job,
a.dt_2025 as first_year_mon,
a.cnt as first_year_cnt,
b.dt_2026 as second_year_mon,
b.cnt as second_year_cnt
from 
    (
    select
    job,
    date_format(date,'%Y-%m') as dt_2025,
    sum(num) as cnt
    from resume_info as a
    where year(date)='2025'
    group by date_format(date,'%Y-%m'),job
    ) as a
join
    (
    select
    job,
    date_format(date,'%Y-%m') as dt_2026,
    sum(num) as cnt
    from resume_info as b
    where year(date)='2026'
    group by date_format(date,'%Y-%m'),job
    ) as b
on a.job=b.job and substring(dt_2025,6,7)=substring(dt_2026,6,7)
order by first_year_mon desc,job desc

SQL281 最差是第几名(一)

1. 因为题目中A B C D是分好类的，所以不再需要用partition by对他再进行分类了

select
grade,
sum(number)over(order by grade) as t_rank
from class_grade

SQL282 最差是第几名(二)

1. 求中位数的方法：正逆向数为总数一半的数为中位数，总数为奇数时中位数只有一个，为偶数时中位数有两个！

select grade 
from 
    (
    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 b
    from class_grade
    ) as c
where a >= total/2 and b >=total/2
order by grade;

SQL283 获得积分最多的人(一)

select 
b.name, 
a.grade
from
    (
    select 
    user_id,
    sum(grade_num)over(partition by user_id) as grade
    from grade_info
    order by grade desc
    limit 1
    ) as a
join user as b on a.user_id = b.id

SQL284 获得积分最多的人(二)

select 
user_id,
name,
grade_sum
from
    (
    select 
    user_id,
    sum(grade_num) as grade_sum,
    rank() over(order by sum(grade_num) desc) as rk
    from grade_info
    where type="add"
    group by user_id
    ) as a 
join user as b on b.id=a.user_id
where rk=1
order by user_id

SQL285 获得积分最多的人(三)

1. 这一题与上一题的区别在于多了一个 reduce，可能会出现减分的情况，因此在计算分数和排名的时候，应该分情况讨论，类型为add时就加，类型为reduce时就减。

select 
user_id,
name,
grade_sum
from
    (
    select 
    user_id,
    sum(if(type='add',grade_num,grade_num*(-1))) as grade_sum,
    rank() over(order by sum(if(type='add',grade_num,grade_num*(-1))) desc) as rk
    from grade_info
    group by user_id
    ) as a 
join user as b on b.id=a.user_id
where rk=1
order by user_id

SQL286 商品交易(网易校招笔试真题

select 
a.id, 
a.name, 
a.weight,
c.total
from goods as a 
join (
    select 
    b.goods_id, 
    sum(b.count) as total
    from trans as b 
    group by b.goods_id
    ) as c 
on a.id = c.goods_id
where a.weight < 50
and c.total > 20
order by id

SQL287 网易云音乐推荐(网易校招笔试真题)

1. 问题：请你编写一个SQL，查询向user_id = 1 的用户，推荐其关注的人喜欢的音乐。不要推荐该用户已经喜欢的音乐，并且按music的id升序排列。
2. 思路：这题不难，就是理解起来比较麻烦，需要灵活的运用这三个表格。首先建立一个表找到user_id为1的人关注的人；然后再建立一张表找到这个人关注的音乐的id；因为不要推荐该用户已经喜欢的音乐，所以需要再建立一个表找到该用户喜欢的音乐，并用条件语句去掉；最后建立一张大表c与前面三张表拼成的表d连接，即可得到1用户关注的人喜欢的音乐。

select music_name
from music as c
join (
    select distinct music_id
    from music_likes
    where user_id in (
        select follower_id
        from follow
        where user_id='1'
        )
        and music_id not in (
        select music_id
        from follow as a
        join music_likes as b on a.user_id=b.user_id
        where a.user_id='1'
        )
    ) as d
on c.id=d.music_id
order by c.id

SQL288 今天的刷题量(一)

1. current_date 返回当前的日期

select
name,
count(subject_id) as cnt
from submission as a
join subject as b on a.subject_id=b.id
where create_time=current_date()
group by name,subject_id
order by cnt desc,subject_id