2026年全网SQL题库附答案

2026年全网SQL题库附答案一、基础操作题1.已知表user_info包含字段：user_id（用户ID，主键）、user_name（用户名）、reg_time（注册时间，datetime类型）、gender（性别，'男'/'女'）。要求查询2025年1月1日至2025年6月30日期间注册的女性用户，按注册时间倒序排列，只显示user_id和user_name。解答：SELECTuser_id,user_nameFROMuser_infoWHEREgender='女'ANDreg_timeBETWEEN'2025-01-0100:00:00'AND'2025-06-3023:59:59'ORDERBYreg_timeDESC;2.表order_detail包含字段：order_id（订单ID）、user_id（用户ID）、product_id（商品ID）、amount（金额，数值型）、create_time（订单时间，datetime）。要求统计每个用户2025年各月的总消费金额，结果包含user_id、月份（格式'2025-01'）、总金额，按总金额降序排列。解答：SELECTuser_id,DATE_FORMAT(create_time,'%Y-%m')ASmonth,SUM(amount)AStotal_amountFROMorder_detailWHEREYEAR(create_time)=2025GROUPBYuser_id,monthORDERBYtotal_amountDESC;二、多表连接与子查询题3.表user_info（同题1）、表order_master（字段：order_id、user_id、order_status（状态，'已支付'/'未支付'）、pay_time（支付时间，datetime））。要求查询2025年支付过订单的用户信息（user_id、user_name、首次支付时间），且用户注册时间早于首次支付时间。解答：WITHfirst_payAS(SELECTuser_id,MIN(pay_time)ASfirst_pay_timeFROMorder_masterWHEREorder_status='已支付'ANDYEAR(pay_time)=2025GROUPBYuser_id)SELECTu.user_id,u.user_name,f.first_pay_timeFROMuser_infouJOINfirst_payfONu.user_id=f.user_idWHEREu.reg_time<f.first_pay_time;4.表product（字段：product_id、product_name、category（品类）、price（单价））、表order_detail（同题2）。要求查询2025年销量（订单中的商品数量，假设每行order_detail对应1件商品）前10的商品，结果包含product_id、product_name、销量，且只保留品类为'电子'的商品。解答：SELECTduct_id,duct_name,COUNT()ASsalesSELECTduct_id,duct_name,COUNT()ASsalesFROMorder_detaildJOINproductpONduct_id=duct_idWHEREp.category='电子'ANDYEAR(d.create_time)=2025GROUPBYduct_id,duct_nameORDERBYsalesDESCLIMIT10;三、窗口函数与复杂分析题5.表user_login_log（字段：user_id、login_time（登录时间，datetime））。要求找出2025年连续7天及以上登录的用户（连续指自然日连续，如1号、2号…7号为连续7天）。解答思路：按用户分组，计算每次登录日期与该用户首次登录日期的差值，若存在连续日期差值等于行号减1的情况，则说明连续。解答：WITHlogin_datesAS(SELECTuser_id,DATE(login_time)ASlogin_dateFROMuser_login_logWHEREYEAR(login_time)=2025GROUPBYuser_id,login_date-去重同一用户同一天多次登录),ranked_datesAS(SELECTuser_id,login_date,ROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYlogin_date)ASrn,DATE_SUB(login_date,INTERVALROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYlogin_date)DAY)ASgrpFROMlogin_dates)SELECTDISTINCTuser_idFROMranked_datesGROUPBYuser_id,grpHAVINGCOUNT()>=7;HAVINGCOUNT()>=7;6.表order_master（同题3）、表order_detail（同题2）。要求计算2025年每个用户的客单价（总支付金额/支付订单数），并按客单价从高到低排序，客单价保留两位小数，只显示客单价大于1000的用户。解答：WITHpay_ordersAS(SELECTo.user_id,COUNT(DISTINCTo.order_id)ASorder_cnt,SUM(d.amount)AStotal_amountFROMorder_masteroJOINorder_detaildONo.order_id=d.order_idWHEREo.order_status='已支付'ANDYEAR(o.pay_time)=2025GROUPBYo.user_id)SELECTuser_id,ROUND(total_amount/order_cnt,2)ASavg_priceFROMpay_ordersWHEREavg_price>1000ORDERBYavg_priceDESC;四、索引与性能优化题7.表user_login_log（同题5）数据量达5000万条，常见查询为“按user_id筛选某时间段内的登录记录”，如何优化查询性能？解答：①为user_id和login_time创建联合索引（user_id,login_time），覆盖常用查询条件，减少回表；②若查询时间范围固定（如按月），可考虑按时间分区（如按月分区），缩小扫描范围；③避免SELECT，只查询需要的字段，减少索引覆盖范围；③避免SELECT，只查询需要的字段，减少索引覆盖范围；④定期归档旧数据（如保留1年内记录），降低表数据量。8.执行以下SQL时效率低下，分析原因并优化：SELECTu.user_name,COUNT(o.order_id)ASorder_cntFROMuser_infouLEFTJOINorder_masteroONu.user_id=o.user_idWHEREo.pay_timeBETWEEN'2025-01-01'AND'2025-12-31'GROUPBYu.user_id,u.user_name;解答：低效原因：①LEFTJOIN后使用WHERE过滤o.pay_time，会将LEFTJOIN转为INNERJOIN（因为o表非NULL的条件），导致无法保留无订单用户；②未对o表的pay_time和user_id建立索引，全表扫描；③GROUPBY包含非主键字段（user_name），可能影响聚合效率。优化方案：①将过滤条件移至JOIN的ON子句，保留无订单用户；②为order_master(user_id,pay_time)创建索引；③仅按user_id分组（因user_id是主键，user_name可通过user_id关联获取）。优化后SQL：SELECTu.user_name,COUNT(o.order_id)ASorder_cntFROMuser_infouLEFTJOINorder_masteroONu.user_id=o.user_idANDo.pay_timeBETWEEN'2025-01-01'AND'2025-12-31'GROUPBYu.user_id;五、业务场景综合题9.电商业务中，表user_behavior（字段：user_id、behavior_type（行为类型，'点击'/'加购'/'购买'）、item_id（商品ID）、time（行为时间，datetime））。要求计算2025年每个商品的“购买转化率”（购买人数/点击人数），结果保留4位小数，只显示转化率>0.1的商品。解答：WITHclick_usersAS(SELECTitem_id,COUNT(DISTINCTuser_id)ASclick_cntFROMuser_behaviorWHEREbehavior_type='点击'ANDYEAR(time)=2025GROUPBYitem_id),buy_usersAS(SELECTitem_id,COUNT(DISTINCTuser_id)ASbuy_cntFROMuser_behaviorWHEREbehavior_type='购买'ANDYEAR(time)=2025GROUPBYitem_id)SELECTc.item_id,ROUND(COALESCE(b.buy_cnt,0)/c.click_cnt,4)ASconversion_rateFROMclick_userscLEFTJOINbuy_usersbONc.item_id=b.item_idWHEREc.click_cnt>0-避免除零错误ANDROUND(COALESCE(b.buy_cnt,0)/c.click_cnt,4)>0.1;10.物流业务中，表delivery（字段：order_id、delivery_id（物流单号）、status（状态，'已揽件'/'运输中'/'已签收'）、status_time（状态变更时间，datetime））。要求查询2025年每个物流单号的“运输时长”（从'已揽件'到'已签收'的时间差，单位：小时），若未签收则不显示，结果按运输时长升序排列。解答：WITH揽件时间AS(SELECTdelivery_id,status_timeASpick_timeFROMdeliveryWHEREstatus='已揽件'ANDYEAR(status_time)=2025),签收时间AS(SELECTdelivery_id,status_timeASsign_timeFROMdeliveryWHEREstatus='已签收'ANDYEAR(status_time)=2025)SELECTp.delivery_id,TIMESTAMPDIFF(HOUR,p.pick_time,s.sign_time)ASdelivery_hoursFROM揽件时间pJOIN签收时间sONp.delivery_id=s.delivery_idWHEREs.sign_time>p.pick_time-确保时间顺序正确ORDERBYdelivery_hoursASC;11.社交业务中，表message（字段：msg_id（消息ID）、sender_id（发送者ID）、receiver_id（接收者ID）、send_time（发送时间，datetime）、is_read（是否已读，0/1））。要求查询2025年每个用户作为发送者时，消息的“24小时未读率”（24小时内未读消息数/24小时内发送的消息总数），结果保留3位小数。解答：WITHsend_msgAS(SELECTsender_id,msg_id,send_time,判断24小时内是否未读：is_read=0且当前时间（或统计截止时间）send_time<=24小时CASEWHENis_read=0ANDsend_time>='2025-12-3123:59:59'INTERVAL24HOURTHEN1ELSE0ENDASunread_24hFROMmessageWHEREYEAR(send_time)=2025),user_statsAS(SELECTsender_id,COUNT()AStotal_24h,COUNT()AStotal_24h,SUM(unread_24h)ASunread_cntFROMsend_msgGROUPBYsender_id)SELECTsender_id,ROUND(unread_cnt/NULLIF(total_24h,0),3)ASunread_rate_24hFROMuser_stats;12.金融业务中，表loan（字段：loan_id（贷款ID）、user_id、amount（贷款金额）、due_date（到期日，date）、repay_date（还款日期，date，NULL表示未还款））。要求统计2025年末“逾期贷款”的用户分布，即按逾期天数（repay_date>due_date的天数）分组，统计每组用户数（同一用户多笔逾期贷款只计1次）。解答：WITHoverdue_loansAS(SELECTuser_id,DATEDIFF(repay_date,due_date)ASoverdue_daysFROMloanWHERErepay_date>due_date-逾期ANDYEAR(due_date)<=2025-2025年末前到期AND(repay_dateISNOTNULL)-已还款但逾期OR(repay_dateISNULLANDdue_date<='2025-12-31')-未还款且已到期),user_overdueAS(SELECTuser_id,未还款的逾期天数按'2025-12-31'计算CASEWHENrepay_dateISNULLTHENDATEDIFF('2025-12-31',due_date)ELSEDATEDIFF(repay_date,due_date)ENDASfinal_overdue_daysFROMloanWHERE(repay_date>due_dateANDYEAR(due_date)<=2025)OR(repay_dateISNULLANDdue_date<='2025-12-31')),distinct_usersAS(SELECTDISTINCTuser_id,final_overdue_daysFROMuser_overdue)SELECTfinal_overdue_days,COUNT(user_id)ASuser_countFROMdistinct_usersGROUPBYfinal_overdue_daysORDERBYfinal_overdue_days;13.教育业务中，表exam_score（字段：student_id、course_id、score（分数，0-100）、exam_time（考试时间，datetime））。要求查询每个学生每门课程的最近一次考试成绩（若同一课程多次考试，取时间最新的），结果包含student_id、course_id、latest_score。解答：WITHranked_examsAS(SELECTstudent_id,course_id,score,ROW_NUMBER()OVER(PARTITIONBYstudent_id,course_idORDERBYexam_timeDESC)ASrnFROMexam_score)SELECTstudent_id,course_id,scoreASlatest_scoreFROMranked_examsWHERErn=1;14.零售业务中，表inventory（字段：product_id、warehouse_id（仓库ID）、stock（库存量）、update_time（更新时间，datetime））。要求查询每个仓库202