SQL 分组查询、HAVING筛选、子查询+SQL 临时表与游标-港品优选

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.StudentId=ScoreList.StudentId inner join Class on Class.ClassId=Students.ClassId

1.2 统计每个班级的C#平均分（基础分组）

按班级名称分组，对每组的成绩计算平均值，分组字段必须出现在SELECT列表中。

--统计每个班级的c#的平均分 --GROUP BY ClassName：按班级名称分组 --AVG(Score)：对每组成绩计算平均值 select Class.ClassName,AVG(Score) as 平均分 from Students inner join ScoreList on Students.StudentId=ScoreList.StudentId inner join Class on Class.ClassId=Students.ClassId group by ClassName --分组字段需出现在SELECT中

1.3 统计每个班级的人数（内连接版，有缺陷）

内连接仅查询有成绩的学生，会漏掉没有成绩（未考试）的学生，导致人数统计不全。

--班级的人数的统计（内连接，有缺陷） --INNER JOIN 取交集，成绩表无记录的学生会被排除 select Class.ClassName,COUNT(*) as 个数 from Students inner join ScoreList on Students.StudentId=ScoreList.StudentId inner join Class on Class.ClassId=Students.ClassId group by ClassName

1.4 统计每个班级的人数（左外连接优化，推荐）

使用左外连接，以学生表为基准，包含所有学生（无论是否有成绩），统计结果更准确。

--班级的人数的统计（优化版：左外连接） --LEFT OUTER JOIN 包含学生表所有记录，避免遗漏未考试学生 select Class.ClassName,COUNT(*) as 个数 from Students left outer join ScoreList on Students.StudentId=ScoreList.StudentId left outer join Class on Class.ClassId=Students.ClassId group by ClassName

1.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.StudentId=ScoreList.StudentId inner join Class on Class.ClassId=Students.ClassId group by ClassName

1.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.StudentId=ScoreList.StudentId left outer join Class on Class.ClassId=Students.ClassId group by ClassName

二、分组后筛选（HAVING）

HAVING 用于对分组后的结果进行筛选，区别于 WHERE（WHERE 筛选分组前的原始数据），必须与 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 null

SQL 临时表与游标

核心考点：临时表创建与删除、游标定义/打开/循环/关闭/释放、游标状态判断（@@FETCH_STATUS）、数据插入临时表的两种方式

适配表结构：ScoreList(StudentId, Score)、Students(StudentId, Name, Age, Sex, Address, ClassId)

一、临时表（#TempTable）

1.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_STATUS=0 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 #TempTable

1.4 向临时表插入数据（方式二：直接插入查询结果，推荐）

通过 INSERT INTO...SELECT 语句，直接将查询结果批量插入临时表，无需逐行处理，效率更高，适用于批量数据插入场景。

--向临时表插入数据：从ScoreList表读取数据（成绩90-100分），批量插入 insert into #TempTable select ScoreList.Score,COUNT(Score)as ScoreCount from ScoreList where Score>90 and Score<=100 --筛选条件：成绩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_STATUS=0）→关闭游标→释放游标。
@@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_STATUS=0 begin --数据处理逻辑（插入、修改、打印等） fetch next from 游标名称 into @变量1,@变量2 --拉取下一行 end --6.关闭游标 close 游标名称 --7.释放游标 deallocate 游标名称 --临时表批量插入模板 insert into #临时表名 select 字段1,字段2 from 表 where 筛选条件

企业官网建设流程全解析