SQL76 考试分数(五)
select id, job, score, `t_rank`from(select id, job, score,row_number() over(partition by job order by score desc) as `t_rank`, count(score) over(partition by job) as numfrom grade) awhere (num % 2 = 1 and `t_rank` = round(num / 2)) or (num % 2 = 0 and `t_rank` = round(num / 2)) or (num % 2 = 0 and `t_rank` = round(num) / 2 + 1)order by id
解题思路：判断中位数：如果是奇数num % 2 = 1 and `t_rank` = round(num / 2)，如果是偶数(num % 2 = 0 and `t_rank` = round(num / 2)) or (num % 2 = 0 and `t_rank` = round(num) / 2 + 1)
select id, job, score, a.`rn`from(select id, job, score,row_number() over(partition by job order by score desc) as `rn`, row_number() over(partition by job order by score) as `dn`, count(score) over(partition by job) as numfrom grade) awhere (num % 2 = 1 and a.`rn` = a.`dn`) or (num % 2 = 0 and abs(cast(a.`rn` as signed) - cast(a.`dn` as signed)) = 1)order by id
解题思路：若排序总数为奇数，则中位数的正序位置与倒序位置相等；若排序总数为偶数，则中位数的正序位置与倒序位置正好差1
SQL77 牛客的课程订单分析(一)
select *from order_infowhere status = 'completed'and date > '2025-10-15'and product_name in ('C++', 'Java', 'Python')order by id
解题思路：用datediff(date1, date2) > 0的形式筛选，找出2025-10-15以后的日期
SQL78 牛客的课程订单分析(二)
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
SQL79 牛客的课程订单分析(三)
select id, user_id, product_name, status, client_id, date from ( select *, count(user_id) over(partition by user_id) as `rank` from order_info where date > '2025-10-15' and product_name in ('C++','Java','Python') and status = 'completed' ) a where a.`rank` >= 2 order by id
SQL80 牛客的课程订单分析(四)
select user_id, date, num from ( select user_id, date, row_number() over(partition by user_id order by date) as `rank`, count(*) over(partition by user_id) as num from order_info where date > '2025-10-15' and status = 'completed' and product_name in ('C++','Java','Python') ) a where a.`rank` = 1 and a.num >= 2 order by user_id
解题思路：用两个窗口函数分别算出符合条件的首次购买课程的日期以及对应的购买次数
