力扣 50-SQL-基础 复盘解析

SQL 学习总结3-4天 50题刷题复盘一、刷题整体收获通过3-4天集中刷完50道SQL题目实现从基础查询到复杂查询的跨越式提升打破了对SQL的固有认知不再局限于简单的查询语句能够主动拆解复杂题目、分步构造查询逻辑读懂并写出中等难度的SQL代码清晰掌握SQL的执行机制与灵活用法形成了结构化的解题思路。二、核心语法突破2.1 SELECT 语句的灵活运用打破“SELECT仅用于查询结果输出”的固有认知掌握SELECT的全场景嵌套用法只要语法合法SELECT可出现在任何需要结果集的位置基础用法SELECT 字段 FROM 表名常规查询子查询嵌套SELECT内部标量子查询SELECT name, (SELECT AVG(salary) FROM employee) AS avg_sal FROM employeeFROM内部派生表SELECT * FROM (SELECT id, name FROM employee WHERE salary 5000) AS tWHERE条件中SELECT * FROM employee WHERE departmentId IN (SELECT id FROM department WHERE name 技术部)2.2 窗口函数核心重点掌握窗口函数的核心语法与适用场景理解“分组但不合并行”的核心逻辑核心通用语法函数()OVER(PARTITIONBY分组列-- 按指定列分组类似GROUP BY但不合并行ORDERBY排序列[ASC/DESC]-- 分组内按指定列排序)2.2.1 三大排名窗口函数函数名称核心特点适用场景示例薪资数据10000, 10000, 8000, 7000ROW_NUMBER()连续不重复相同值也分配唯一序号无并列去重、取分组内唯一一条数据、分页排名结果1, 2, 3, 4RANK()相同值并列同名次后续名次跳号断档标准排名场景允许名次断档排名结果1, 1, 3, 4DENSE_RANK()相同值并列同名次后续名次连续不断档TOPN题型如部门工资前三高保留并列数据排名结果1, 1, 2, 3实战示例部门工资前三高SELECTd.nameASDepartment,e.nameASEmployee,e.salaryASSalaryFROM(SELECTname,salary,departmentId,DENSE_RANK()OVER(PARTITIONBYdepartmentIdORDERBYsalaryDESC)ASrkFROMEmployee)eJOINDepartment dONe.departmentIdd.idWHEREe.rk3;2.2.2 聚合函数 OVER()聚合函数SUM、AVG、COUNT等可结合OVER()使用核心优势不合并行保留原始明细同时获取分组统计结果。常用聚合窗口函数SUM()、AVG()、COUNT()、MAX()、MIN()实战示例获取每个人工资及所在部门的平均工资、总工资SELECTname,departmentId,salary,AVG(salary)OVER(PARTITIONBYdepartmentId)ASdept_avg_sal,-- 部门平均工资SUM(salary)OVER(PARTITIONBYdepartmentId)ASdept_total_sal-- 部门总工资FROMEmployee;2.3 WITH 子句虚拟表/CTE核心作用拆分复杂SQL逻辑替代FROM子句中的多层嵌套子查询使代码结构清晰、易读、易调试、可复用。语法格式WITH虚拟表1AS(SELECT...-- 第一步查询逻辑),虚拟表2AS(SELECT...-- 第二步查询逻辑可引用虚拟表1)-- 最终查询可引用所有虚拟表SELECT*FROM虚拟表1JOIN虚拟表2ON...;实战示例筛选重复的2015保额和唯一经纬度计算2016保额总和WITHsame_2015AS(-- 重复的2015保额SELECTtiv_2015FROMInsuranceGROUPBYtiv_2015HAVINGCOUNT(*)2),unique_locAS(-- 唯一的经纬度SELECTlat,lonFROMInsuranceGROUPBYlat,lonHAVINGCOUNT(*)1)SELECTROUND(SUM(tiv_2016),2)AStiv_2016FROMInsuranceWHEREtiv_2015IN(SELECTtiv_2015FROMsame_2015)AND(lat,lon)IN(SELECTlat,lonFROMunique_loc);2.4 连接查询自连接、笛卡尔积2.4.1 自连接核心一张表自己与自己连接别名区分如t1、t2用于实现同一表内不同行的比较、滑动窗口、行与行配对。实战示例7天滑动平均营业额WITHtAS(SELECTvisited_on,SUM(amount)ASamountFROMCustomerGROUPBYvisited_on)SELECTt1.visited_on,SUM(t2.amount)ASamount,ROUND(SUM(t2.amount)/7,2)ASaverage_amountFROMt t1JOINt t2ONDATEDIFF(t1.visited_on,t2.visited_on)BETWEEN0AND6GROUPBYt1.visited_onHAVINGCOUNT(DISTINCTt2.visited_on)7ORDERBYt1.visited_on;2.4.2 笛卡尔积核心两张表连接时不写ON条件会产生两张表的所有行组合全匹配加ON条件后可实现范围匹配、连续日期计算等场景。注意笛卡尔积容易产生大量数据需配合条件过滤使用避免性能问题。2.5 集合运算交集、并集运算类型关键字核心特点示例并集UNION合并两个结果集自动去重SELECT id FROM A UNION SELECT id FROM B并集UNION ALL合并两个结果集不去重效率更高SELECT id FROM A UNION ALL SELECT id FROM B交集INTERSECT返回两个结果集的共同数据SELECT id FROM A INTERSECT SELECT id FROM B实战示例统计所有参与好友申请的用户IDSELECTrequester_idASidFROMRequestAcceptedUNIONSELECTaccepter_idASidFROMRequestAccepted;三、常用函数详解附示例3.1 日期函数函数名称功能说明示例示例结果DATEDIFF(date1, date2)计算date1与date2的天数差date1 - date2DATEDIFF(‘2024-01-07’, ‘2024-01-01’)6DATE_SUB(date, INTERVAL n 单位)给日期减去n个指定单位单位DAY、MONTH等DATE_SUB(‘2024-01-07’, INTERVAL 3 DAY)2024-01-04DATE_ADD(date, INTERVAL n 单位)给日期增加n个指定单位DATE_ADD(‘2024-01-07’, INTERVAL 2 DAY)2024-01-093.2 字符串函数函数名称功能说明示例示例结果GROUP_CONCAT(字段)分组内将指定字段的多行数据合并成一行字符串默认用逗号分隔GROUP_CONCAT(name) FROM employee GROUP BY departmentId张三,李四,王五3.3 数字函数函数名称功能说明示例示例结果ROUND(数值, 小数位数)将数值四舍五入到指定小数位数ROUND(2800/7, 2)400.003.4 其他常用函数COUNT()统计行数示例COUNT(DISTINCT t2.visited_on)统计不重复的日期数量SUM()求和示例SUM(t2.amount)计算7天营业额总和四、高级技巧与注意事项4.1 多列匹配支持多字段同时作为条件进行匹配常用于坐标、组合条件筛选语法(字段1,字段2)IN(SELECT字段1,字段2FROM表名WHERE...)示例筛选经纬度唯一的记录SELECT*FROMInsuranceWHERE(lat,lon)IN(SELECTlat,lonFROMInsuranceGROUPBYlat,lonHAVINGCOUNT(*)1);4.2 WHERE 与 HAVING 的区别关键字过滤时机过滤对象是否支持聚合函数WHERE分组之前原始行数据不支持HAVING分组之后分组后的聚合结果支持示例筛选出部门人数大于3的部门需用HAVINGSELECTdepartmentId,COUNT(*)ASdept_countFROMEmployeeGROUPBYdepartmentIdHAVINGCOUNT(*)3;4.3 SQL 执行顺序SQL语句的书写顺序与执行顺序不同正确执行顺序如下从左到右FROM指定查询的表JOIN连接多张表ON连接条件过滤WHERE过滤原始行数据GROUP BY按指定列分组HAVING过滤分组后的聚合结果SELECT筛选需要的字段ORDER BY对结果排序五、整体总结通过3-4天50道题的集中练习已全面掌握中等难度SQL的核心知识点能够独立解决力扣中等难度SQL题目具体掌握内容如下灵活运用SELECT语句掌握子查询的全场景嵌套用法精通三大排名窗口函数与聚合窗口函数理解窗口函数的核心逻辑熟练使用WITH子句拆分复杂查询提升代码可读性与可维护性掌握自连接、笛卡尔积的实际用途能够解决行与行比较、滑动窗口等问题掌握集合运算交集、并集能够处理多结果集的合并与筛选熟练使用日期、字符串、数字等常用函数应对各类数据处理场景掌握多列匹配、WHERE与HAVING区别等高级技巧理解SQL执行顺序形成结构化解题思路能够拆解复杂题目、分步构造查询逻辑。后续可继续深入学习行列 能力。