27,579
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (A varchar(1),B int)
insert into #T
select 'a',1 union all
select 'a',2 union all
select 'a',3 union all
select 'a',4 union all
select 'a',5 union all
select 'b',2 union all
select 'b',3 union all
select 'b',4 union all
select 'b',5 union all
select 'b',9 union all
select 'b',10 union all
select 'd',10 union all
select 'd',12 union all
select 'd',13 union all
select 'd',14
select * from #T as t where B in (select top 3 B from #T where A=t.A order by B)
/*
A B
a 1
a 2
a 3
b 2
b 3
b 4
d 10
d 12
d 13
*/
create table ta (A varchar(1),B int)
insert into ta
select 'a',1 union all
select 'a',2 union all
select 'a',3 union all
select 'a',4 union all
select 'a',5 union all
select 'b',2 union all
select 'b',3 union all
select 'b',4 union all
select 'b',5 union all
select 'b',9 union all
select 'b',10 union all
select 'd',10 union all
select 'd',12 union all
select 'd',13 union all
select 'd',14
select a.a,a.b
from ta a
join ta b
on a.a = b.a
group by a.a,a.b
having count(case when a.b >= b.b then 1 else null end) < = 3 --可动态修改
order by a.a,a.b
drop table ta
/*
a b
---- -----------
a 1
a 2
a 3
b 2
b 3
b 4
d 10
d 12
d 13
(所影响的行数为 9 行)
*/
create table ta (A varchar(1),B int)
insert into ta
select 'a',1 union all
select 'a',2 union all
select 'a',3 union all
select 'a',4 union all
select 'a',5 union all
select 'b',2 union all
select 'b',3 union all
select 'b',4 union all
select 'b',5 union all
select 'b',9 union all
select 'b',10 union all
select 'd',10 union all
select 'd',12 union all
select 'd',13 union all
select 'd',14
select a.a,a.b
from ta a
join ta b
on a.a = b.a
group by a.a,a.b
having count(case when a.b <= b.b then 1 else null end) < = 3 --可动态修改
order by a.a,a.b desc
drop table ta
/*
a b
---- -----------
a 5
a 4
a 3
b 10
b 9
b 5
d 14
d 13
d 12
*/
--测试环境
create table student --学生列表
(
s_id int,
s_name char(10)
)
create table class --课程列表
(
c_id int,
c_name char(10)
)
create table grade --学生成绩表
(
s_id int,
c_id int,
cj int
)
--测试数据
insert student values(1,'A')
insert student values(2,'B')
insert student values(3,'C')
insert student values(4,'D')
insert student values(5,'E')
insert class values(1,'历史')
insert class values(2,'数学')
insert grade values(1,1,60)
insert grade values(2,1,70)
insert grade values(3,1,80)
insert grade values(4,1,90)
insert grade values(5,1,100)
insert grade values(1,2,85)
insert grade values(2,2,67)
insert grade values(3,2,94)
insert grade values(4,2,63)
insert grade values(5,2,87)
select a.c_id,a.s_id,a.cj from grade a
join grade b
on a.c_id = b.c_id
group by a.c_id,a.s_id,a.cj
having count(case when a.cj <= b.cj then 1 else null end) < = 5 --可动态修改
order by a.c_id,a.cj desc
--处理重复分数
select a.c_id,a.s_id,a.cj
from grade a join
(
select c_id,cj
from grade
group by c_id,cj
) b
on a.c_id = b.c_id
group by a.c_id,a.s_id,a.cj
having count(case when a.cj <= b.cj then 1 else null end) < = 5 --可动态修改
order by a.c_id,a.cj desc
--你可以以动态的修改: n <= n 来获得
--每门课程的前n个最高分的
--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (A varchar(1),B int)
insert into #T
select 'a',1 union all
select 'a',1 union all
select 'a',2 union all
select 'a',3 union all
select 'a',4 union all
select 'a',5 union all
select 'b',2 union all
select 'b',3 union all
select 'b',3 union all
select 'b',4 union all
select 'b',5 union all
select 'b',9 union all
select 'b',10 union all
select 'd',10 union all
select 'd',12 union all
select 'd',12 union all
select 'd',12 union all
select 'd',13 union all
select 'd',14
--> 哦,去重复:
select distinct A, B from #T as t where B in (select distinct top 3 B from #T where A=t.A) order by A, B
/*
A B
a 1
a 2
a 3
b 2
b 3
b 4
d 10
d 12
d 13
*/