sql刷题3

牛客每次考试完，都会有一个成绩表(grade)，如下:

第1行表示用户id为1的用户选择了C++岗位并且考了11001分
。。。
第8行表示用户id为8的用户选择了前端岗位并且考了9999分
请你写一个sql语句查询用户分数大于其所在工作(job)分数的平均分的所有grade的属性，并且以id的升序排序，如下

(注意: sqlite 1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5，sqlite四舍五入的函数为round)

-- 窗口函数. 对job集每个行记录中进行均值计算
select id, job, score
from
  (select id,job,score,
    avg(score)over(partition by job)av
   from `grade`
  )t1
where score > av
order by 1 ;

-- 头铁的人就是直接子查询怼上去
select 
  id,
  job, 
  score 
from `grade` t1
where score >
  (select avg(score) from `grade` t2 where t1.job = t2.job)
order by
  1 ;

-- 更头铁的人直接连表
select id, t1.job, score
from `grade` t1
  left join
  (select job,avg(score)av from `grade` group by 1)t2
  on t1.job = t2.job
where t1.score > t2.av
order by 1 ;

如下有一张商品表（goods），字段依次为：商品id、商品名、商品质量

还有一张交易表（trans），字段依次为：交易id、商品id、这个商品购买个数

查找购买个数超过20,质量小于50的商品，按照商品id升序排序,如:

思路：
trans表只需要提供每个商品的购买总数，所以嵌套查询，1先处理好trans表，命名为b(select子查询)，2再结合goods表完成任务(外层select)
程序：

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

有很多同学在牛客购买课程来学习，购买会产生订单存到数据库里。

有一个订单信息表(order_info)，简况如下

第1行表示user_id为557336的用户在2025-10-10的时候使用了client_id为1的客户端下了C++课程的订单，但是状态为没有购买成功。
第2行表示user_id为230173543的用户在2025-10-12的时候使用了client_id为2的客户端下了Python课程的订单，状态为购买成功。
。。。
最后1行表示user_id为557336的用户在2025-10-25的时候使用了client_id为1的客户端下了Python课程的订单，状态为购买成功。
请你写出一个sql语句查询在2025-10-15以后，如果有一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程，那么输出这个用户的user_id，以及满足前面条件的第一次购买成功的C++课程或Java课程或Python课程的日期first_buy_date，以及购买成功的C++课程或Java课程或Python课程的次数cnt，并且输出结果按照user_id升序排序，以上例子查询结果如下:

解析:

id为4，6的订单满足以上条件，输出57，id为4的订单为第一次购买成功，输出first_buy_date为2025-10-23，总共成功购买了2次;

id为5，7，8的订单满足以上条件，输出557336，id为5的订单为第一次购买成功，输出first_buy_date为2025-10-23，总共成功购买了3次
实这个题目订单(二)和订单(三)筛选的东西，聚合之后是一样的，所以根据订单(二)的写法，加聚合更简单，

select user_id,min(date) first_buy_date,count(user_id) 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

有一个员工表employees简况如下:

有一个部门表departments表简况如下:

有一个，部门员工关系表dept_emp简况如下:

请你查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工，以上例子输出如下:

本题思路为运用两次LEFT JOIN连接嵌套

1、第一次LEFT
JOIN连接employees表与dept_emp表，得到所有员工的last_name和first_name以及对应的dept_no，也包括暂时没有分配部门的员工

2、第二次LEFT
JOIN连接上表与departments表，即连接dept_no与dept_name，得到所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工

SELECT em.last_name, em.first_name, dp.dept_name
FROM (employees AS em LEFT JOIN dept_emp AS de ON em.emp_no = de.emp_no)
LEFT JOIN departments AS dp ON de.dept_no = dp.dept_no

牛客每次考试完，都会有一个成绩表(grade)，如下:

第1行表示用户id为1的用户选择了C++岗位并且考了11001分。。。
第8行表示用户id为8的用户选择了B语言岗位并且考了9999分
请你写一个sql语句查询各个岗位分数升序排列之后的中位数位置的范围，并且按job升序排序，结果如下

解释:

第1行表示C++岗位的中位数位置范围为[2,2]，也就是2。因为C++岗位总共3个人，是奇数，所以中位数位置为2是正确的(即位置为2的10000是中位数)
第2行表示Java岗位的中位数位置范围为[1,2]。因为Java岗位总共2个人，是偶数，所以要知道中位数，需要知道2个位置的数字，而因为只有2个人，所以中位数位置为[1,2]是正确的(即需要知道位置为1的12000与位置为2的13000才能计算出中位数为12500)
第3行表示前端岗位的中位数位置范围为[2,2]，也就是2。因为B语言岗位总共3个人，是奇数，所以中位数位置为2是正确的(即位置为2的11000是中位数)
(注意: sqlite 1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5，sqlite四舍五入的函数为round，sqlite不支持floor函数，支持cast(x as integer) 函数，不支持if函数，支持case when …then …else …end函数)
难道不是 总数/2 (总数+1)除以2 吗

select a.job, round(count(a.id)/2), round((count(a.id)+1)/2)
from grade a
group by a.job
order by job
Start部分向下取整，End部分向上取整

select job,floor((count(job)+1)/2) as start,ceiling((count(job)+1)/2) as end
from grade
group by job
order by job

有很多同学在牛客购买课程来学习，购买会产生订单存到数据库里。

有一个订单信息表(order_info)，简况如下:

第1行表示user_id为557336的用户在2025-10-10的时候使用了client_id为1的客户端下了C++课程的订单，但是状态为没有购买成功。
第2行表示user_id为230173543的用户在2025-10-12的时候使用了client_id为2的客户端下了Python课程的订单，状态为购买成功。
。。。
最后1行表示user_id为557336的用户在2025-10-25的时候使用了client_id为1的客户端下了C++课程的订单，状态为购买成功。
请你写出一个sql语句查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的user_id，并且按照user_id升序排序，以上例子查询结果如下:

这道题就是在第一题的基础上加一个group by分组，再将聚合函数count(user_id)放入到having关键字之后进行筛选，要注意的就是聚合函数只能用having筛选，非聚合函数更推荐用where筛选，因为where效率更高

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

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