牛客SQL 全部题目 SQL266-SQL277 12套代码及解析（7）

SQL266 考试分数(一)

select job,
round(avg(score),3) as avg
from grade
group by job
order by avg desc

SQL267 考试分数(二)

1. 题目要求：用户分数大于其所在工作(job)分数的平均分的所有grade的属性；以id的升序排序
2. 思路：建立2个表，一个是按照工作聚合求分数平均值的表，一个是用户分数表，使两个表连接。当用户分数表大于平均值表时，即可求得我们想要的答案。

select 
id,
a.job,
score
from grade as a
join 
(select job,avg(score) as avg_score  from grade group by job) as b 
on a.job=b.job
where score>avg_score
order by id

SQL268 考试分数(三)

1. 不能在WHERE子句中使用窗口函数 不能在HAVING子句中使用窗口函数 不能在GROUP BY子句中使用窗口函数

select a.id,b.name,a.score
from language as b
join 
    (select id,	language_id	,score,
    dense_rank()over(partition by language_id order by score desc) as rk
    from grade) as a
on a.language_id=b.id
where rk<=2
order by name,score desc,a.id

1. 题目要求：1.请你找出每个岗位分数排名前2名的用户； 2.得到的结果先按照language的name升序排序，再按照积分降序排序，最后按照grade的id升序排序
2. 排名前2名，题目中给出的结果示例是1，2，2的形式，因此可以用dense_rank
3. 方法一方法二都是一个思路，就是再创建一个表，在新的表中用dense_rank进行排名，因为窗口函数不能直接与where使用，所以在表外设置排名小于2，即可得到每个岗位分数排名前2名的用户。方法一二的区别在于：一个是连接，一个是子查询。

select id,name,score
from
    (select a.id,name,score,
    dense_rank()over(partition by language_id order by score desc) as rk
    from grade as a
    join language as b
    on a.language_id=b.id) as c
where rk<=2
order by name,score desc,id

SQL269 考试分数(四)

1. floor() 函数返回小于或等于指定值（value）的最小整数,取整,没有四舍五入这一说法 floor(103.56) 返回 103
   floor(103.46) 返回 103

select job,
    floor(( count(*) + 1 )/ 2 ) AS "start",
    floor(( count(*) + 2 )/ 2 ) AS 'end'
from grade
group by job
order by job
	floor() 函数返回小于或等于指定值（value）的最小整数,取整,没有四舍五入这一说法 floor(103.56)  返回 103 floor(103.46)  返回 103

SQL270 考试分数(五)

1. 题目要求：查询各个岗位分数的中位数位置上的所有grade信息，并且按id升序排序
2. 思路：上一题其实已经给出了算中位数的方法，我们只要按照岗位聚合，再求出排名就可以了。首先，建立一个表，按照上一题的方法求出中位数并按照岗位聚合。因为where中不能使用窗口函数，所以我们需要再建立一张表与之连接，这张表要用row_number求出排名。然后用where使第一张中位数的排名等于第二张表的排名即可得到按照岗位聚合后的中位数排名的信息。最后直接select从这两张连接的表中取出想要的答案。

select a.id,a.job,a.score,a.t_rank
from (
    select id,job,score,
    row_number()over(partition by job order by score desc) as t_rank
    from grade) as a
join (    
    select job,
    floor((count(*)+1)/2) as t_rank1,
    floor((count(*)+2)/2) as t_rank2
    from grade
    group by job) as b
on a.job=b.job
where t_rank=b.t_rank1 or t_rank=b.t_rank2
order by id

SQL271 牛客的课程订单分析(一)

select id ,user_id,product_name,status,client_id,date
from order_info
where date>'2025-10-15' and status='completed' and product_name in ('C++','Java','Python')
order by id

SQL272 牛客的课程订单分析(二)

select user_id
from order_info
where date>'2025-10-15' 
and status='completed' 
and product_name in ('C++','Java','Python')
group by user_id
having count(user_id)>=2
order by user_id

SQL273 牛客的课程订单分析(三)

1. 这一题因为取的字段更多，所以不能像上一题一样直接使用group by了（因为会出现full group by
   报错），需要单独再建立一个表，在这个表里只取user_id字段，并用where取条件 下单2个及以上。
2. 问：为什么第二个表里也要加入where的那些条件只设置user_id大于等于2不行吗？答：先用内表找出符合条件的user_id，然后才能用外表找出该user_id符合的记录

select *
from order_info 
where date>'2025-10-15' 
and status='completed' 
and product_name in ('C++','Java','Python')
and user_id in (
    select user_id
    from order_info
    where date>'2025-10-15' 
    and status='completed' 
    and product_name in ('C++','Java','Python')
    group by user_id
    having count(user_id)>=2
    ) 
order by id

SQL274 牛客的课程订单分析(四)

select 
user_id,
min(date) as first_buy_date,
count(user_id) as cnt
from order_info
where date > '2025-10-15'
and product_name in ('C++','Java','Python')
and status = 'completed'
group by user_id
having count(user_id) > 1
order by user_id

SQL275 生客的课程订单分析(五)

1. 题目仅要求返回第1、2次购买时间，故只需返回前两条记录，时间最小为第1次，时间最大为第2次，用row_number进行排序，用where筛选出前两条记录，即可得到第1、2次的购买时间。

select
 user_id,
 max(case when rank_no=1 then date else 0 end) as first_buy_date,
 max(case when rank_no=2 then date else 0 end) as second_buy_date,
cnt
from
    (select
     user_id,
     date,
     row_number() over(partition by user_id order by date) as rank_no,
     count(*) over(partition by user_id) as cnt
    from order_info
    where date >= '2025-10-16'
      and status = 'completed'
      and product_name in('C++','Java','Python')
    ) as a
where rank_no<=2 and cnt>=2
group by user_id,cnt
order by user_id 

SQL276 牛客的课程订单分析(六)

1. 题目要求“最后一列如果是非拼团订单，则显示对应客户端名字，如果是拼团订单，则显示NULL”,直接用一个表查的话很容易出现full group by的问题，遇到这种情况 嵌套一个仅一个字段的子查询就可以了

select 
a.id,
is_group_buy,
name as client_name 
from order_info as a
left join client as b 
on a.client_id = b.id
where date > '2025-10-15'
and status = 'completed'
and product_name in ('C++','Java','Python')
and user_id in
    (
    select user_id
    from order_info
    where date > '2025-10-15'
    and status = 'completed'
    and product_name in ('C++','Java','Python')
    group by user_id
    having count(status) >= 2
    )
order by a.id

1. 右连接+窗口函数解答该问题
2. 为什么不能左连接：因为窗口函数不能直接和where一起使用，所有窗口函数要嵌套在里面，用外面的where对查询进行限制。

select b.id,b.is_group_buy,a.name as client_name
from client as a right join
    (
    select *,count(id) over(partition by user_id) as number
    from order_info
    where datediff(date,"2025-10-15")>0
      and status="completed"
      and product_name in ("C++","Java","Python")
    ) as b
on a.id=b.client_id
where b.number >1
order by b.id

SQL277 牛客的课程订单分析(七)

select 
if(a.client_id = 0, 'GroupBuy', c.name) as source, 
count(*) as cnt
from order_info as a
left join client as c on a.client_id = c.id
where a.status = 'completed'
      and a.date >= '2025-10-15'
      and a.product_name in ('C++', 'Java', 'Python')
      and user_id in 
      (
      select user_id
      from order_info as a
      where a.status = 'completed'
        and a.date >= '2025-10-15'
        and a.product_name in ('C++', 'Java', 'Python')
      group by user_id
      having count(*) > 1
      ) 
group by a.client_id
order by source