高手帮忙（高分回报）

[splory]

我建的表如下
table sc (stuid int,subjectid int,score float)
table stu (stuid int,stuname varchar(10),classid int)
table subject (subjectid int,subjectname varchar(10))
你们插入些数据就成


我想做下述要求的查询，对其中的一个班级的学生，求出此班级各科的前十名，查询结果大概如下：
名次 语文 数学 英语
1 99 98 99
2 97 95 98
3 94 94 97
4 90 91 93

各位大佬帮忙啊，谢谢了

[splory]

数据为：

set nocount on
Create table sc (stuid int,subjectid int,score float)
insert sc values(0101,1,75.0)
insert sc values(0102,1,70.0)
insert sc values(0103,1,90.0)
insert sc values(0104,1,88.5)
insert sc values(0105,1,90.0)
insert sc values(0106,1,79.0)
insert sc values(0201,1,89.0)
insert sc values(0101,2,89.0)
insert sc values(0102,2,80.0)
insert sc values(0103,2,99.0)
insert sc values(0104,2,98.0)
insert sc values(0105,2,67.0)
insert sc values(0106,2,86.0)
insert sc values(0201,2,99.0)
insert sc values(0101,3,89.0)
insert sc values(0102,3,79.0)
insert sc values(0103,3,75.0)
insert sc values(0104,3,86.0)
insert sc values(0105,3,79.5)
insert sc values(0106,3,97.0)
insert sc values(0201,3,87.0)
insert sc values(0101,4,99.0)
insert sc values(0102,4,73.0)
insert sc values(0103,4,89.5)
insert sc values(0104,4,88.0)
insert sc values(0105,4,98.0)
insert sc values(0106,4,86.0)
insert sc values(0201,4,95.0)
insert sc values(0101,5,67.0)
insert sc values(0102,5,87.0)
insert sc values(0103,5,84.5)
insert sc values(0104,5,98.0)
insert sc values(0105,5,100.0)
insert sc values(0106,5,97.0)
insert sc values(0201,5,97.0)
insert sc values(0101,6,85.0)
insert sc values(0102,6,96.0)
insert sc values(0103,6,93.0)
insert sc values(0104,6,86.0)
insert sc values(0105,6,89.0)
insert sc values(0106,6,89.0)
insert sc values(0201,6,91.5)
Create table stu (stuid int,stuname varchar(10),classid int)
insert stu values(101,'张三',1)
insert stu values(102,'李四',1)
insert stu values(103,'王五',1)
insert stu values(104,'小白',1)
insert stu values(105,'宋华',1)
insert stu values(106,'钱一',1)
insert stu values(201,'赵善',2)
Create table subject (subjectid int,subjectname varchar(10))
insert subject values(1,'语文')
insert subject values(2,'数学')
insert subject values(3,'英语')
insert subject values(4,'物理')
insert subject values(5,'化学')
insert subject values(6,'生物')

[子陌红尘]

declare @s varchar(8000)
set @s='select 名次'

select @s=@s+',['+subjectname+']=max(case subjectid when '+rtrim(subjectid)+' then score end)' from subject

set @s=@s+' from (select b.*,(select count(*) from sc where subjectid=b.subjectid and (score>b.score or (score=b.score and stuid<b.stuid)))+1 as 名次 from sc b) a group by 名次 order by 名次'

exec(@s)

/*
名次 语文 数学 英语 物理 化学 生物
----------- ---------- ---------- ---------- ---------- ---------- ----------
1 90 99 97 99 100 96
2 90 99 89 98 98 93
3 89 98 87 95 97 91.5
4 88.5 89 86 89.5 97 89
5 79 86 79.5 88 87 89
6 75 80 79 86 84.5 86
7 70 67 75 73 67 85
*/

[splory]

后十名的又怎么搞？

[子陌红尘]

declare @s varchar(8000)
set @s='select 名次'

select @s=@s+',['+subjectname+']=cast(max(case subjectid when '+rtrim(subjectid)+' then score end) as varchar(6))' from subject

set @s=@s+' from (select b.*,(select count(*) from sc where subjectid=b.subjectid and (score<b.score or (score=b.score and stuid<b.stuid)))+1 as 名次 from sc b) a group by 名次 order by 名次'

exec(@s)

/*
名次 语文 数学 英语 物理 化学 生物
----------- ---------- ---------- ---------- ---------- ---------- ----------
1 70 67 75 73 67 85
2 75 80 79 86 84.5 86
3 79 86 79.5 88 87 89
4 88.5 89 86 89.5 97 89
5 89 98 87 95 97 91.5
6 90 99 89 98 98 93
7 90 99 97 99 100 96
*/

[wds1728]

这个主要就是一个表对某一字段的排序问题
求前十名时为
select c.stuid,c.score,b.classid,b.stuname into #t from sc as c inner join stu as b on(c.stuid=b.stuid and b.classid=@classid)
--select * from #t

select (select sum(1) from (select classid,stuid,aa=sum(score) from #t group by classid,stuid) a1 where sum(a.score)<=aa and a1.classid=a.classid) paiming,stuname,stuid,classid,sum(score) score into #paiming
from #t a group by stuid,classid,stuname

--select * from #paiming
--drop table #t drop table #paiming

declare @s varchar(8000),@s2 varchar(8000)
set @s='select t1.*,t2.总分 from (select top 10 [名次]=''前''+rtrim(paiming)'

select @s=@s+',['+subjectname+']=max(case subjectid when '+rtrim(subjectid)+' then stuname+''(''+rtrim(score)+'')'' end)' from subject

set @s=@s+' from (select b.*,c.stuname,(select count(*) from sc,stu where subjectid=b.subjectid and sc.stuid=stu.stuid and stu.classid='+rtrim(@classid)+' and (score>b.score or (score=b.score and sc.stuid<b.stuid)))+1 as paiming from stu c,sc b where c.stuid=b.stuid and c.classid='+rtrim(@classid)+') a group by paiming order by paiming) t1'
--print @s
set @s2=' inner join (select top 10 [名次]=''前''+rtrim(paiming),paiming,[总分]=stuname+''(''+rtrim(score)+'')'' from #paiming order by paiming) t2 on t1.名次=t2.名次 order by t2.paiming drop table #t drop table #paiming'
--print @s+@s2
exec(@s+@s2)

求后十名时只需要改score>b.score 改为score<b.score就成
这只是个排序问题