create table tb(编号 int ,学号 varchar(50),课程名 varchar(50),成绩 int)
insert into tb select 1,'001','英语',90
insert into tb select 2,'001','数学',100
insert into tb select 3,'001','C#',95
insert into tb select 4,'002','英语',100
insert into tb select 5,'003','vb.net',23
insert into tb select 6,'003','vfp',60
insert into tb select 7,'003','数据结构',70
select * from tb t where exists(
select * from tb where 学号=t.学号
and 成绩>=t.成绩
group by 学号
having count(*)<=2
)编号 学号 课程名 成绩
2 001 数学 100
3 001 C# 95
4 002 英语 100
6 003 vfp 60
7 003 数据结构 70
insert into tb select 1,'001','英语',90
insert into tb select 2,'001','数学',100
insert into tb select 3,'001','C#',95
insert into tb select 4,'002','英语',100
insert into tb select 5,'003','vb.net',23
insert into tb select 6,'003','vfp',60
insert into tb select 7,'003','数据结构',70
select * from tb t where exists(
select * from tb where 学号=t.学号
and 成绩>=t.成绩
group by 学号
having count(*)<=2
)编号 学号 课程名 成绩
2 001 数学 100
3 001 C# 95
4 002 英语 100
6 003 vfp 60
7 003 数据结构 70
--ignore my answer if your version lower than 2005.
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([编号] int,[学号] varchar(3),[课程名] varchar(8),[成绩] int)
insert [tb]
select 1,'001','英语',90 union all
select 2,'001','数学',100 union all
select 3,'001','C#',95 union all
select 4,'002','英语',100 union all
select 5,'003','vb.net',23 union all
select 6,'003','vfp',60 union all
select 7,'003','数据结构',70select [编号],[学号],[课程名],[成绩]
from (select *,rn=row_number() over(partition by [学号] order by [成绩] desc) from tb) t
where rn<3
order by [编号]--测试结果:
/*
编号 学号 课程名 成绩
----------- ---- -------- -----------
2 001 数学 100
3 001 C# 95
4 002 英语 100
6 003 vfp 60
7 003 数据结构 70(5 行受影响)
*/
create table tb(编号 int ,学号 varchar(50),课程名 varchar(50),成绩 int)
insert into tb select 1,'001','英语',90
insert into tb select 2,'001','数学',100
insert into tb select 3,'001','C#',95
insert into tb select 4,'002','英语',100
insert into tb select 5,'003','vb.net',23
insert into tb select 6,'003','vfp',60
insert into tb select 7,'003','数据结构',70select 编号,学号,课程名,成绩 from (
select 编号,学号,课程名,成绩, row_number() over(partition by 学号 order by 成绩 desc) as rn
from tb group by 学号,课程名,编号,成绩) as b where b.rn<3/*---
2 001 数学 100
3 001 C# 95
4 002 英语 100
7 003 数据结构 70
6 003 vfp 60
insert into tb select 1,'001','英语',90
insert into tb select 2,'001','数学',100
insert into tb select 3,'001','C#',95
insert into tb select 4,'002','英语',100
insert into tb select 5,'003','vb.net',23
insert into tb select 6,'003','vfp',60
insert into tb select 7,'003','数据结构',70select t.* from tb t where 成绩 in (select top 2 成绩 from tb where 学号 = t.学号 order by 成绩 desc)drop table tb/*
编号 学号 课程名 成绩
----------- -------------------------------------------------- -------------------------------------------------- -----------
2 001 数学 100
3 001 C# 95
4 002 英语 100
6 003 vfp 60
7 003 数据结构 70（所影响的行数为 5 行）
*/
insert into tb select 1,'001','英语',90
insert into tb select 2,'001','数学',100
insert into tb select 3,'001','C#',95
insert into tb select 4,'002','英语',100
insert into tb select 5,'003','vb.net',23
insert into tb select 6,'003','vfp',60
insert into tb select 7,'003','数据结构',70
go--方法1
select a.* from tb a where 2 > (select count(*) from tb where 学号 = a.学号 and 成绩 > a.成绩 ) order by a.学号,a.成绩 desc
--方法2
select a.* from tb a where 成绩 in (select top 2 成绩 from tb where 学号=a.学号 order by 成绩 desc) order by a.学号,a.成绩 desc
--方法3
select a.* from tb a where exists (select count(*) from tb where 学号 = a.学号 and 成绩 > a.成绩 having Count(*) < 2) order by a.学号,a.成绩 descdrop table tb/*
编号 学号 课程名 成绩
----------- -------------------------------------------------- -------------------------------------------------- -----------
2 001 数学 100
3 001 C# 95
4 002 英语 100
7 003 数据结构 70
6 003 vfp 60（所影响的行数为 5 行）
*/