沧州做网站推广公司,网站的基础服务,网站建设安全制度图片,江岸网站建设一、分组后找出指定序号的数据 【需求】查出每个班级第三个注册入学的学生信息 【表和字段】Student: ID Class Name Registrationtime 【实现SQL】
WITH RankedStudents AS (SELECT ID,Class,Name,Registrationtime,ROW_NUMBER() OVER(PARTITION BY Class ORDER BY Registra…一、分组后找出指定序号的数据 【需求】查出每个班级第三个注册入学的学生信息 【表和字段】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。
