一、分组后找出指定序号的数据
【需求】查出每个班级第三个注册入学的学生信息
【表和字段】Student: ID Class Name Registrationtime
【实现SQL】
WITH RankedStudents AS (SELECT ID,Class,Name,Registrationtime,ROW_NUMBER() OVER(PARTITION BY Class ORDER BY Registrationtime) AS RowNum --根据班级分组,注册时间排序赋序号FROM Student
)
SELECT ID, Class, Name, Registrationtime
FROM RankedStudents
WHERE RowNum = 3;
【讲解】
ROW_NUMBER() OVER(PARTITION BY Class ORDER BY Registrationtime) AS RowNum
这是窗口函数 ROW_NUMBER() 的应用。它根据 Class 分区,并按 Registrationtime 升序排列,为每个分区中的行分配一个序号 RowNum,表示在每个班级内按注册时间排序的顺序。以便后面我们选出序号=3的学生。
二、分组后找出指定条件指定序号的数据
【需求】查出每个班级第三个注册入学的男学生信息,如果没有那么多男学生,则取第三个注册入学的学生(无论性别)的信息
【表和字段】Student: ID Class Name Sex Registrationtime
【实现SQL】
WITH RankedStudents AS (SELECT ID,Class,Name,Sex,Registrationtime,ROW_NUMBER() OVER(PARTITION BY Class, Sex ORDER BY Registrationtime) AS RowNum,COUNT(CASE WHEN Sex = '男' THEN 1 END) OVER(PARTITION BY Class) AS MaleCount --按班级分组,COUNT出每个班男学生人数FROM Student
),
ThirdMaleOrThirdStudent AS (SELECT ID,Class,Name,Sex,Registrationtime,RowNum,MaleCount,CASE WHEN MaleCount >= 3 AND Sex = '男' AND RowNum = 3 THEN 1 --男学生多且序号为3则赋Selected序号为1WHEN MaleCount < 3 AND RowNum = 3 THEN 1 --男学生少且存在第三个学生则赋Selected序号为1ELSE 0END AS SelectedFROM RankedStudents
)
SELECT ID, Class, Name, Sex, Registrationtime
FROM ThirdMaleOrThirdStudent
WHERE Selected = 1;--选出Selected序号为1的信息
【讲解】
COUNT(CASE WHEN Sex = '男' THEN 1 END) OVER(PARTITION BY Class) AS MaleCount
这是根据 Class 列进行分区计数,计算每个班级中男生的数量,并将结果命名为 MaleCount
另外:
1.第一个临时表 RankedStudents作用是为每个班每个学生分配一个排名,并计算每个班级中男生的总数。
2.第二个临时表 ThirdMaleOrThirdStudent作用是根据特定条件标记第三个男生或第三个学生。
3.最后一个部分是主查询,它从 ThirdMaleOrThirdStudent 中选择符合条件 Selected = 1 的学生信息。
4.CASE WHEN的作用:相当于程序中的IF-ELSE。