SQL 分组查询、HAVING筛选、子查询核心考点GROUP BY分组查询、HAVING分组筛选、子查询IN/NOT IN、内连接与外连接在分组中的区别、聚合函数结合分组使用适配表结构Students(StudentId, Name, Age, Sex, ClassId)、ScoreList(StudentId, Score)、Class(ClassId, ClassName)一、分组查询基础GROUP BY分组查询用于将数据按指定字段分组对每组数据单独进行聚合统计如计数、求和、平均值等核心语法GROUP BY 分组字段。关键规则SELECT 后面的字段要么是分组字段要么是聚合函数否则会报错。1.1 基础多表关联查询铺垫查询每个学生的姓名、年龄、C#成绩及班级信息为后续分组做基础。--查询每个学生的姓名 年龄 c#成绩 以及班级信息 --三表内连接学生表关联成绩表、班级表 select Students.Name,Age,Score,ClassName from Students inner join ScoreList on Students.StudentIdScoreList.StudentId inner join Class on Class.ClassIdStudents.ClassId1.2 统计每个班级的C#平均分基础分组按班级名称分组对每组的成绩计算平均值分组字段必须出现在SELECT列表中。--统计每个班级的c#的平均分 --GROUP BY ClassName按班级名称分组 --AVG(Score)对每组成绩计算平均值 select Class.ClassName,AVG(Score) as 平均分 from Students inner join ScoreList on Students.StudentIdScoreList.StudentId inner join Class on Class.ClassIdStudents.ClassId group by ClassName --分组字段需出现在SELECT中1.3 统计每个班级的人数内连接版有缺陷内连接仅查询有成绩的学生会漏掉没有成绩未考试的学生导致人数统计不全。--班级的人数的统计内连接有缺陷 --INNER JOIN 取交集成绩表无记录的学生会被排除 select Class.ClassName,COUNT(*) as 个数 from Students inner join ScoreList on Students.StudentIdScoreList.StudentId inner join Class on Class.ClassIdStudents.ClassId group by ClassName1.4 统计每个班级的人数左外连接优化推荐使用左外连接以学生表为基准包含所有学生无论是否有成绩统计结果更准确。--班级的人数的统计优化版左外连接 --LEFT OUTER JOIN 包含学生表所有记录避免遗漏未考试学生 select Class.ClassName,COUNT(*) as 个数 from Students left outer join ScoreList on Students.StudentIdScoreList.StudentId left outer join Class on Class.ClassIdStudents.ClassId group by ClassName1.5 单表按性别分组统计人数单表分组按性别Sex分组统计每组的学生数量无需多表关联。--单表按照性别分组统计男、女生人数 select Sex,COUNT(*) as 人数 from Students group by Sex --按性别字段分组1.6 统计班级人数、最高分、最低分、平均分内连接版结合多个聚合函数COUNT、MAX、MIN、AVG按班级分组统计同样存在遗漏未考试学生的问题。--查询班级的人数、C#最高分、最低分、平均分内连接版 select Class.ClassName, COUNT(*) as 个数, Max(Score) as 最高分, Min(Score) as 最小值, Avg(Score) as 平均值 from Students inner join ScoreList on Students.StudentIdScoreList.StudentId inner join Class on Class.ClassIdStudents.ClassId group by ClassName1.7 统计班级人数、最高分、最低分、平均分左外连接优化使用左外连接包含所有学生手动计算平均分Sum(Score)/COUNT(*)避免AVG函数忽略NULL值的问题。--查询班级的人数、C#最高分、最低分、平均分左外连接优化版 select Class.ClassName, COUNT(*) as 个数, Max(Score) as 最高分, Min(Score) as 最小值, Sum(Score)/ COUNT(*) as 平均值 --手动计算包含所有学生 from Students left outer join ScoreList on Students.StudentIdScoreList.StudentId left outer join Class on Class.ClassIdStudents.ClassId group by ClassName二、分组后筛选HAVINGHAVING 用于对分组后的结果进行筛选区别于 WHEREWHERE 筛选分组前的原始数据必须与 GROUP BY 配合使用。2.1 筛选重复的成绩记录按成绩分组筛选出出现次数大于1的成绩即重复的成绩。--使用having对分组之后数据再进行过滤查找重复的成绩记录 --GROUP BY Score按成绩分组 --HAVING COUNT(Score)1筛选出出现次数大于1的成绩 select Score,COUNT(*) as 重复次数 from ScoreList group by Score having COUNT(Score)1三、子查询嵌套查询子查询是在一个查询语句中嵌入另一个查询语句内层查询的结果作为外层查询的条件核心语法IN / NOT IN (子查询语句)。3.1 查询性别重复的学生记录内层查询先筛选出重复的性别出现次数≥1外层查询根据内层结果查询对应学生记录。--查询性别重复的记录 --内层子查询按性别分组筛选出出现次数≥1的性别此处所有性别都满足可改为≥2筛选重复 --外层查询根据子查询结果查询对应学生信息 select * from Students where Sex in (select Sex from Students group by Sex having count(Sex)1) --拓展筛选出现次数≥2的性别真正的重复 --select * from Students where Sex in (select Sex from Students group by Sex having count(Sex)2)3.2 子查询检索未考试的学生NOT IN内层查询获取有成绩的学生学号外层查询筛选出不在该列表中的学生即未考试学生。--检索学生没有考试的个数及详细记录NOT IN --内层子查询获取成绩表中有成绩的学生学号 --外层查询筛选出学号不在子查询结果中的学生未考试 select COUNT(*) as 未考试人数 from Students where StudentId not in (select StudentId from ScoreList) select * from Students where StudentId not in (select StudentId from ScoreList)3.3 外连接查询未考试学生替代子查询通过左外连接筛选出成绩表中成绩为NULL的记录同样可查询未考试学生与子查询结果一致。--使用外连接查询未考试学生的记录替代子查询 --左外连接包含所有学生Score为NULL的即为未考试学生 select * from Students left outer join ScoreList on Students.StudentId ScoreList.StudentId where ScoreList.Score is null --筛选成绩为空的记录四、核心考点汇总必背GROUP BY 规则SELECT 后的字段要么是分组字段要么是聚合函数否则报错。WHERE 与 HAVING 区别WHERE 筛选分组前的原始数据HAVING 筛选分组后的统计结果HAVING 必须配合 GROUP BY 使用。内连接与外连接在分组中的区别内连接仅统计有关联数据的记录外连接可统计所有基准表记录避免遗漏。子查询IN 用于匹配子查询结果中的值NOT IN 用于排除子查询结果中的值可替代外连接实现部分查询功能。聚合函数结合分组COUNT(*)统计总行数、MAX/MIN求极值、AVG求平均值、SUM求和可同时使用多个聚合函数。五、易错踩坑点分组查询时忘记将分组字段写在 SELECT 列表中导致语法错误。混淆 WHERE 和 HAVING 的使用场景用 HAVING 筛选原始数据或用 WHERE 筛选分组结果。使用内连接统计人数时遗漏未考试的学生应优先使用左外连接。子查询中使用聚合函数时忘记配合 GROUP BY导致子查询结果异常。AVG 函数会自动忽略 NULL 值手动计算平均分时需注意包含所有记录如用 Sum/Count(*)。六、语法速记模板--分组查询模板 select 分组字段, 聚合函数(字段) as 别名 from 表1 join 表2 on 关联条件 group by 分组字段 having 聚合函数(字段) 筛选条件 --子查询模板IN select * from 表 where 字段 in (select 字段 from 表 group by 字段 having 筛选条件) --子查询模板NOT IN select * from 表 where 字段 not in (select 字段 from 关联表) --外连接查询未关联记录模板 select * from 表1 left join 表2 on 关联条件 where 表2.字段 is nullSQL 临时表与游标核心考点临时表创建与删除、游标定义/打开/循环/关闭/释放、游标状态判断FETCH_STATUS、数据插入临时表的两种方式适配表结构ScoreList(StudentId, Score)、Students(StudentId, Name, Age, Sex, Address, ClassId)一、临时表#TempTable1.1 临时表核心概念临时表是临时存储数据的表并非真实存在于数据库中而是存储在tempdb系统数据库中会话结束后自动销毁类似C#中的临时变量用于临时存储查询结果方便后续操作。临时表命名规则以#开头仅当前会话可见若以##开头为全局临时表所有会话可见。1.2 临时表创建与删除规范写法创建临时表前需先判断是否存在若存在则删除避免重复创建报错。--判断临时表是否存在存在则删除防止重复创建报错 --OBJECT_ID(tempdb..#TempTable)查询tempdb中是否存在#TempTable临时表 if OBJECT_ID(tempdb..#TempTable) is not null drop table #TempTable --删除临时表 --创建临时表定义字段及约束 create table #TempTable ( Score int not null, --成绩字段非空约束 ScoreCount int null --成绩出现次数允许为空 )1.3 向临时表插入数据方式一通过游标插入游标用于逐行读取查询结果将每行数据插入临时表适用于需要逐行处理数据的场景。--1. 定义变量用于接收游标读取的数据 declare Score int; --存储成绩 declare ScoreCount int; --存储成绩出现次数 --2. 定义游标Score_cursor为游标名称关联查询结果按成绩分组统计次数 declare Score_cursor cursor for select ScoreList.Score,COUNT(Score)as ScoreCount from ScoreList group by Score --3. 打开游标启动游标准备读取数据 open Score_cursor --4. 游标初始化指向结果集第一行将第一行数据赋值给变量 --fetch next拉取结果集下一行数据 fetch next from Score_cursor into Score,ScoreCount --5. 循环游标逐行读取数据并插入临时表 --FETCH_STATUS系统内置变量判断游标读取状态0读取成功-1读取失败-2行不存在 while FETCH_STATUS0 begin --将当前行数据插入临时表 insert into #TempTable(Score,ScoreCount) values(Score,ScoreCount); --拉取下一行数据为下一次循环做准备 fetch next from Score_cursor into Score,ScoreCount end --6. 关闭游标释放游标占用的资源禁止后续读取 close Score_cursor --7. 释放游标彻底销毁游标释放所有相关资源 deallocate Score_cursor --查询临时表中的数据验证插入结果 select * from #TempTable1.4 向临时表插入数据方式二直接插入查询结果推荐通过 INSERT INTO...SELECT 语句直接将查询结果批量插入临时表无需逐行处理效率更高适用于批量数据插入场景。--向临时表插入数据从ScoreList表读取数据成绩90-100分批量插入 insert into #TempTable select ScoreList.Score,COUNT(Score)as ScoreCount from ScoreList where Score90 and Score100 --筛选条件成绩90到100分 group by Score --按成绩分组统计次数 --查询临时表数据验证插入结果 select * from #TempTable二、游标Cursor核心详解游标是SQL中用于逐行处理查询结果集的工具适用于需要对每行数据单独操作的场景如逐行打印、逐行修改、逐行插入核心分为6个步骤定义→打开→初始化→循环→关闭→释放。2.1 游标完整使用示例查询学生信息并逐行打印--1. 定义变量接收游标读取的学生信息 declare StudentId int; --学生ID declare StudentName varchar(20);--学生姓名 declare Age int; --年龄 declare Sex nvarchar(10); --性别 declare Address nvarchar(10); --地址 declare ClassId nvarchar(10); --班级ID --2. 定义游标My_Cursor为游标名称关联查询结果学生ID≥3的学生 declare My_Cursor cursor for select * from Students where StudentId 3; --3. 打开游标启动游标准备读取数据 open My_Cursor; --4. 游标初始化指向结果集第一行将数据赋值给对应变量 fetch next from My_Cursor into StudentId, StudentName, Age, Sex, Address, ClassId; --5. 循环游标逐行读取并处理数据 --fetch_status系统内置变量判断游标读取状态 --0读取成功-1读取失败或无更多行-2读取的行不存在 while fetch_status 0 begin --逐行打印学生信息 print StudentId; --打印学生ID print StudentName; --打印学生姓名 print Age; --打印年龄 print --------; --打印分隔符 --拉取下一行数据为下一次循环做准备 fetch next from My_Cursor into StudentId, StudentName, Age, Sex, Address, ClassId; end --6. 关闭游标释放游标占用的资源 close my_cursor; --7. 释放游标彻底销毁游标释放所有相关资源 deallocate my_cursor;三、核心考点汇总必背临时表以#开头存储在tempdb中会话结束后自动销毁创建前需判断是否存在避免重复创建。游标六步骤定义游标→打开游标→初始化游标fetch next→循环处理while FETCH_STATUS0→关闭游标→释放游标。FETCH_STATUS系统内置变量0表示读取成功-1表示读取失败/无更多行-2表示读取的行不存在。变量区分单个开头为自定义变量如Score两个开头为系统内置变量如FETCH_STATUS。临时表数据插入方式游标逐行插入适用于逐行处理、INSERT INTO...SELECT批量插入效率高推荐。四、易错踩坑点创建临时表前未判断是否存在导致重复创建报错需先执行DROP TABLE。游标循环中忘记写“fetch next”语句导致死循环必须在循环体内拉取下一行数据。混淆游标关闭CLOSE和释放DEALLOCATE的区别关闭后可重新打开释放后需重新定义。自定义变量与游标读取的字段类型、数量不匹配导致赋值失败。批量插入数据时忽略临时表的字段约束如非空约束导致插入失败。五、语法速记模板--临时表创建模板 if OBJECT_ID(tempdb..#临时表名) is not null drop table #临时表名 create table #临时表名 ( 字段1 类型 约束, 字段2 类型 约束 ) --游标使用模板 --1.定义变量 declare 变量1 类型, 变量2 类型 --2.定义游标 declare 游标名称 cursor for select 字段1,字段2 from 表 where 筛选条件 --3.打开游标 open 游标名称 --4.初始化游标 fetch next from 游标名称 into 变量1,变量2 --5.循环处理 while FETCH_STATUS0 begin --数据处理逻辑插入、修改、打印等 fetch next from 游标名称 into 变量1,变量2 --拉取下一行 end --6.关闭游标 close 游标名称 --7.释放游标 deallocate 游标名称 --临时表批量插入模板 insert into #临时表名 select 字段1,字段2 from 表 where 筛选条件