巴中学校网站建设衡阳有线宽带网站

一、分组后找出指定序号的数据

【需求】查出每个班级第三个注册入学的学生信息
【表和字段】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。