27,579
社区成员
发帖
与我相关
我的任务
分享
create table Student --学生成绩表
(
id int, --主键
Grade int, --班级
Gender nvarchar --性别
)
go
insert Student
select 1,1,'女'
union all select 2,1,'女'
union all select 3,1,'男'
union all select 4,1,'男'
union all select 5,1,'男'
union all select 6,2,'女'
union all select 7,2,'女'
union all select 8,2,'男'
union all select 9,2,'男'
union all select 10,2,'男'
select 序号=row_number() over(order by id),
班级=Grade,
性别=Gender
from (select *,row_number() over(partition by Grade order by Gender desc) 'rn'
from Student) t
where rn<=3
/*
序号 班级 性别
-------------------- ----------- ----
1 1 女
2 1 女
3 1 男
4 2 女
5 2 女
6 2 男
(6 row(s) affected)
*/
select 序号=row_number() over(order by id),
班级=Grade,
性别=Gender
from (select *,row_number() over(partition by Grade order by Gender) 'rn'
from Student) t
where rn<=3
/*
序号 班级 性别
-------------------- ----------- -----------
1 1 1
2 1 1
3 1 2
4 2 1
5 2 1
6 2 2
(6 row(s) affected)
*/
SELECT * FROM (
SELECT *,ROW_NUMBER() OVER (PARTITION BY Grade ORDER BY CASE Gender WHEN '女' THEN 0 ELSE 1 END) AS RN
FROM #Student) a
WHERE a.RN <= 3