一次性查询出每个科目的成绩第一的学生,在SQL SERVER中建立下表,查询出每个科目成绩第一的学生信息.
ID 自动编号
cj 成绩
km 科目
declare @t table(id int,cj int,km varchar(10))
insert @t
select 1, 60, '01 '
union all select 2, 69, '01 '
union all select 3, 70, '02 '
union all select 4, 79, '02 '
union all select 5, 80, '03 '
union all select 6, 89, '03 '
union all select 7, 89, '03 '
--成绩并列时只取一个
select * from @t a
where a.id=(select top 1 id from @t b where b.km=a.km order by b.cj desc,b.id desc)
--成绩并列时全取
select a.* from @t a ,@t b
where a.km=b.km and b.cj<a.cj
select * from @t a
where exists(select 1 from @t b where b.km = a.km and b.cj < a.cj)
select * from @t a
where a.cj=(select max(b.cj) from @t b where b.km = a.km)