有一张表
学生id,学生name,课程id,课程name,课程成绩
1 小李 1 语文 88
1 小李 2 数学 77
2 小张 1 语文 46
3 小何 1 语文 66
.......
如何能用一个sql语句将每门课的前三名选出
学生name 课程name 成绩
小李 语文 88
小何 语文 66
小张 语文 46
小李 数学 77 请高手帮我用MS-SERVER SQL 写一下,先谢了...
学生id,学生name,课程id,课程name,课程成绩
1 小李 1 语文 88
1 小李 2 数学 77
2 小张 1 语文 46
3 小何 1 语文 66
.......
如何能用一个sql语句将每门课的前三名选出
学生name 课程name 成绩
小李 语文 88
小何 语文 66
小张 语文 46
小李 数学 77 请高手帮我用MS-SERVER SQL 写一下,先谢了...
http://topic.csdn.net/u/20080123/18/9731d130-0d4b-4c11-8d89-f2c3ca331f0c.html
if not object_id('cj')is null
drop table cj
go
create table cj(stuName nvarchar(10),KCM nvarchar(10),cj numeric(5,2))
insert into cj select '张三','语文',98
union select '李四','语文',89
union select '王五','语文',67
union select '周攻','语文',56
union select '张三','数学',89
union select '李四','数学',78
union select '王五','数学',90
union select '周攻','数学',87
方法一:
select stuname from
(select stuName,kcm,(select count(*) from cj where stuname!=a.stuname and kcm=a.kcm and cj>a.cj) cnt from cj a) x
group by stuname having max(cnt)<=1
go
方法二:
SELECT stuname FROM cj1 a
where cj IN(SELECT TOP 2 cj FROM cj1 WHERE kcm=a.kcm ORDER BY cj desc)
GROUP BY stuname HAVING(count(1)>1)
方法三:
select distinct stuname from cj a
where not exists(select kcm from cj b where a.stuname=stuname
and (select count(*) from cj where kcm=b.kcm and stuname!=a.stuname and cj>b.cj)>1)
insert into cj select '张三','语文',98
union select '李四','语文',89
union select '王五','语文',67
union select '王一','语文',100
union select '周攻','语文',56
union select '张三','数学',89
union select '李四','数学',78
union select '王五','数学',90
union select '周攻','数学',87
select * from cj aa where cj in(select top 3 cj from cj where kcm=aa.kcm order by cj desc )
order by kcm
drop table cj
go
create table cj(stuName nvarchar(10),KCM nvarchar(10),cj numeric(5,2))
insert into cj select '张三','语文',98
union select '李四','语文',89
union select '王五','语文',67
union select '周攻','语文',56
union select '张三','数学',89
union select '李四','数学',78
union select '王五','数学',90
union select '周攻','数学',87 select stuName,KCM,cj from (
SELECT *, row_number() over (partition by KCM order by cj desc) b
FROM cj)a where b between 1 and 3