SQL刷题_牛客_SQL热题

SQL201 查找入职员工时间排名倒数第三的员工所有信息【简单】SQL201 查找入职员工时间排名倒数第三的员工所有信息窗口函数其他方法# 怎么算倒数第三 如果倒数第123都是同一个日期那算谁# 从运行结果倒推看日期需要去重select*fromemployeeswherehire_date(selectdistincthire_datefromemployeesorderbyhire_datedesclimit1offset2# limit 2, 1)orderbyemp_no# 还可以用 group by 代替 distinctselecthire_datefromemployeesgroupbyhire_dateorderbyhire_dateSQL216 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary【简单】SQL216 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary# select emp_no, salary# from (# select emp_no, salary,# dense_rank() over(order by salary desc) rk# from salaries# ) a# where rk 2selectemp_no,salaryfromsalarieswheresalary(selectdistinctsalaryfromsalariesorderbysalarydesclimit1,1)selectemp_no,max(salary)fromsalarieswheresalary(selectmax(salary)fromsalarieswhereto_date9999-01-01)SQL217 【禁用order by】获取当前薪水第二多的员工的emp_no以及其对应的薪水salarySQL217 获取当前薪水第二多的员工的emp_no以及其对应的薪水salarywitht1as(selecta.emp_no,salary,last_name,first_namefromemployees aleftjoinsalaries bona.emp_nob.emp_nowheresalary!(selectmax(salary)fromsalaries))selectemp_no,salary,last_name,first_namefromt1wheresalary(selectmax(salary)fromt1)SQL288 最大值SQL288 最大值参考上面几题的解法除了窗口函数还可以 降序排列后limit 1我的selectname,cum_sumasgrade_numfrom(selectname,cum_sum,row_number()over(orderbycum_sumdesc)asrkfrom(selectname,sum(if(typeadd,grade_num,-grade_num))over(partitionbyname)ascum_sumfromuseraleftjoingrade_info bona.idb.user_id)a)bwhererk1评论区selectname,sum(grade_num)over(partitionbyuser_id)asgrade_sumfromgrade_info,userwheregrade_info.user_iduser.idorderbygrade_sumdesclimit1SQL289 最大值不唯一select distinct 和 窗口函数写在一行经测试发现distinct能对窗口函数的结果去重selectid,name,cum_sumasgrade_numfrom(selectdistinctid,name,cum_sum,dense_rank()over(orderbycum_sumdesc)asrkfrom(selecta.id,name,sum(if(typeadd,grade_num,-grade_num))over(partitionbyname)ascum_sumfromuseraleftjoingrade_info bona.idb.user_id)a)bwhererk1orderbyid满足条件时窗口函数也可以和group by一起用selectid,name,total_sumasgrade_numfrom(selecta.id,name,sum(if(typeadd,grade_num,-grade_num))total_sum,dense_rank()over(orderbysum(if(typeadd,grade_num,-grade_num))desc)rkfromuseraleftjoingrade_info bona.idb.user_idgroupbya.id,name)awhererk1orderbyidSQL220 查找在职员工自入职以来的薪水涨幅情况SQL220 查找在职员工自入职以来的薪水涨幅情况我的:# 忽略降薪# select emp_no, max(salary) - min(salary) growth# from salaries# group by emp_no# having max(to_date) 9999-01-01# order by growth# 虽然通过了但感觉也忽略了现在比入职时工资低的情况selecta.emp_no,max(salary)-min(salary)growthfrom(selectemp_no,max(to_date)max_dt,min(from_date)min_dtfromsalariesgroupbyemp_nohavingmax(to_date)9999-01-01)aleftjoinsalaries bona.emp_nob.emp_noand(to_datemax_dtorfrom_datemin_dt)groupbya.emp_noorderbygrowthselecta.emp_no,b.salary-c.salary growthfrom(selectemp_no,max(to_date)max_dt,min(from_date)min_dtfromsalariesgroupbyemp_nohavingmax(to_date)9999-01-01)aleftjoinsalaries bona.emp_nob.emp_noandb.to_datemax_dtleftjoinsalaries cona.emp_noc.emp_noandc.from_datemin_dtorderbygrowth评论区selectb.emp_no,(b.salary-a.salary)asgrowthfrom(selecte.emp_no,s.salaryfromemployees eleftjoinsalaries sone.emp_nos.emp_noande.hire_dates.from_date)a-- 入职工资表innerjoin(selecte.emp_no,s.salaryfromemployees eleftjoinsalaries sone.emp_nos.emp_nowheres.to_date9999-01-01)b-- 现在工资表ona.emp_nob.emp_noorderbygrowth-- 另一个selects1.emp_no,(s1.salary-e1.salary)asgrowthfrom# 查询当前工资(selectemp_no,salaryfromsalarieswhereto_date9999-01-01)ass1# 内连接2次查询结果innerjoin# 查询入职时候的工资(selects.emp_no,s.salaryfromsalariesassinnerjoinemployeesaseons.emp_noe.emp_noands.from_datee.hire_date)ase1ons1.emp_noe1.emp_noorderbygrowthSQL274 中位数的位置SQL274 中位数的位置selectjob,ceil(cnt/2)asstart,if(cnt%20,ceil(cnt/2),ceil(cnt/2)1)asendfrom(selectjob,count(1)ascntfromgradegroupbyjob)aorderbyjob# 评论区SELECTjob,floor((count(*)1)/2)ASstart,floor((count(*)2)/2)ASendFROMgradeGROUPBYjobORDERBYjob FLOOR(count(job)/20.5),CEIL(count(job)/20.5)另外参考SQL287selectjob,min(c1)asstart,max(c1)asendfrom(selectjob,count(1)over(partitionbyjoborderbyscore)c1,count(1)over(partitionbyjoborderbyscoredesc)c2,count(1)over(partitionbyjob)totalfromgrade)awherec1total/2andc2total/2groupbyjobSQL287 找聚合数据的中位数找聚合数据的中位数我的selectgradefrom(selectgrade,sum(number)over(orderbygrade)-number1asl,sum(number)over(orderbygrade)r,sum(number)over()totalfromclass_grade)awhere(floor(total/20.5)betweenlandr)or(ceil(total/20.5)betweenlandr)orderbygrade评论区selectgradefrom(selectgrade,sum(number)over(orderbygradeasc)asascnum,sum(number)over(orderbygradedesc)asdescnum,(selectsum(number)fromclass_grade)astotalfromclass_grade)subwhereascnumtotal/2anddescnumtotal/2orderbygradeasc;SQL280 第一次和第二次成功SQL280我的selectdistinctuser_id,first_buy_date,second_buy_date,cntfrom(select*,min(date)over(partitionbyuser_id)asfirst_buy_date,nth_value(date,2)over(partitionbyuser_idorderbydaterowsbetweenunboundedprecedingandunboundedfollowing)second_buy_date,count(1)over(partitionbyuser_id)ascntfromorder_infowheredate2025-10-15andstatuscompletedandproduct_namein(C,Python,Java))awherecnt1AI优化SELECTuser_id,first_buy_date,second_buy_date,cntFROM(SELECTuser_id,MIN(date)OVER(PARTITIONBYuser_id)asfirst_buy_date,-- 【修正点 1】补充第二个参数 2NTH_VALUE(date,2)OVER(PARTITIONBYuser_idORDERBYdateROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING)assecond_buy_date,COUNT(1)OVER(PARTITIONBYuser_id)ascnt,-- 【优化点】添加行号用于精准取第一条替代 DISTINCTROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYdate)asrnFROMorder_infoWHEREdate2025-10-15ANDstatuscompletedANDproduct_nameIN(C,Python,Java))aWHEREcnt1ANDrn1;-- 【优化点 2】直接取每组第一行避免 DISTINCT 的全局去重开销评论区selecta.user_id,min(a.date)asfirst_buy_date,max(a.date)assecond_buy_date,a.cntfrom-- 每个id取两条数据(selectuser_id,date,row_number()over(partitionbyuser_idorderbydate)asrank_no,count(*)over(partitionbyuser_id)ascntfromorder_infowheredate2025-10-16andstatuscompletedandproduct_namein(C,Java,Python))awherea.rank_no2anda.cnt2groupbya.user_id,a.cntorderbya.user_id;SQL292 推荐音乐SQL292请你编写一个SQL查询向user_id 1 的用户推荐其关注的人喜欢的音乐。不要推荐该用户已经喜欢的音乐并且按music的id升序排列。你返回的结果中不应当包含重复项我的第一反应是“我”喜欢的音乐被关注的人喜欢的音乐取差集但是会出现笛卡尔积所以把“我”喜欢的音乐行转列做成了列表只保留被关注的人喜欢的音乐的行数据selectmusic_namefrom(selectdistinctmusic_id,music_name-- select f.user_id, f.follower_id, um_ids, music_id as fm_id, music_namefromfollow f-- 连接被关注的人 喜欢的音乐leftjoinmusic_likes mlonf.follower_idml.user_idleftjoinmusic monm.idmusic_idleftjoin(-- “我”喜欢的音乐列转行后的列表selectuser_id,group_concat(music_id)asum_idsfrommusic_likesgroupbyuser_id)tont.user_idf.user_id# where coalesce(concat(,,um_ids, ,),-999) not like concat(%,, music_id, ,%)# 正确且稳健的写法 不包含在“我”喜欢的音乐列表里WHERECONCAT(,,COALESCE(um_ids,),,)NOTLIKECONCAT(%,,music_id,,%)andf.user_id1)agroupbymusic_id,music_nameorderbymusic_id题解注意distinct必须放在子查询里外层才能在id不出现在select后面时写在order by里SELECTmusic_nameFROM(SELECTDISTINCTm.id,m.music_nameFROMmusic_likes ml-- 我关注的人喜欢的音乐idJOINfollow fONml.user_idf.follower_idJOINmusic mONml.music_idm.idWHEREf.user_id1-- 我关注的人喜欢的音乐id不在我的音乐id里ANDml.music_idNOTIN(SELECTmusic_idFROMmusic_likesWHEREuser_id1))ASsubqueryORDERBYid;