高分急求一个统计表的查询

Question

declare @sql varchar (8000)
select @sql='select cast(b.classid as varchar)+''班'' 班级'
select 
    @sql=@sql+',['+a.subjectname+'] =''(''+cast( max(case c.subjectid when '+rtrim(a.subjectid) +' then name else '''' end) as varchar)+'')'''
from 
    (select distinct c.* from sc b,subject c where c.subjectid=b.subjectid) a
order by a.subjectid
set @sql=@sql+' from(
select stu.classid, name = stu.stuname+quotename(sc.score, ''()''), sub.subjectname, 
from sc, stu, subject sub
where sc.stuid = stu.stuid
    and sc.subjectid = sub.subjectid
    and not exists(
        select * from sc sc1, stu stu1
        where sc1.stuid = stu1.stuid
              and sc1.score > sc.score
              and sc1.subjectid = sc.subjectid 
              and stu1.classid = stu.classid)
)a group by classid'
print convert(varchar(8000),@sql)
exec(@sql)

Answer 1

如果并列，只显示一个名字的：
declare @sql varchar (8000)
select @sql='select cast(b.classid as varchar)+''班'' 班级'
select 
    @sql=@sql+',['+a.subjectname+'] =cast( max(case b.subjectid when '+rtrim(a.subjectid) +' then b.stuname else null end) as varchar)+''(''+cast( max(case c.subjectid when '+rtrim(a.subjectid) +' then c.score else 0 end) as varchar)+'')'''
from 
    (select distinct c.* from sc b,subject c where c.subjectid=b.subjectid) a
order by a.subjectid
set @sql=@sql+' from stu b,sc c where b.stuid=c.stuid and not exists ( select 1 from stu b1,sc c1 where b1.stuid=c1.stuid and c1.classid=c.classid and b1.subjectid=b.subjectid and c1.score>c.score) group by b.classid'
print convert(varchar(8000),@sql)
exec(@sql)

Answer 2

zjcxc(邹建)大哥的是SQL2005版本的,我使用不了
Yang_(扬帆破浪)大哥好像有错阿,老大你再改改阿

Answer 3

我执行得到的为:
select cast(b.classid as varchar)+'班' 班级,[语文] =cast( max(case b.subjectid when 1 then b.stuname else null end) as varchar)+'('+cast( max(case c.subjectid when 1 then c.score else 0 end) as varchar)+')',[数学] =cast( max(case b.subjectid when 2 then b.stuname else null end) as varchar)+'('+cast( max(case c.subjectid when 2 then c.score else 0 end) as varchar)+')',[英语] =cast( max(case b.subjectid when 3 then b.stuname else null end) as varchar)+'('+cast( max(case c.subjectid when 3 then c.score else 0 end) as varchar)+')',[物理] =cast( max(case b.subjectid when 4 then b.stuname else null end) as varchar)+'('+cast( max(case c.subjectid when 4 then c.score else 0 end) as varchar)+')',[化学] =cast( max(case b.subjectid when 5 then b.stuname else null end) as varchar)+'('+cast( max(case c.subjectid when 5 then c.score else 0 end) as varchar)+')',[生物] =cast( max(case b.subjectid when 6 then b.stuname else null end) as varchar)+'('+cast( max(case c.subjectid when 6 then c.score else 0 end) as varchar)+')' from stu b,sc c where b.stuid=c.stuid and not exists ( select 1 from stu b1,sc c1 where b1.stuid=c1.stuid and c1.classid=c.classid and b1.subjectid=b.subjectid and c1.score>c.score) group by b.classid
服务器: 消息 207，级别 16，状态 3，行 1
列名 'subjectid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'classid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'classid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'subjectid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'subjectid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'classid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'classid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'subjectid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'subjectid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'subjectid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'classid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'classid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'subjectid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'subjectid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'classid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'classid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'subjectid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'subjectid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'subjectid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'classid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'classid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'subjectid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'subjectid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'classid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'classid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'subjectid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'subjectid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'subjectid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'classid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'classid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'subjectid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'subjectid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'classid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'classid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'subjectid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'subjectid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'subjectid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'classid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'classid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'subjectid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'subjectid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'classid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'classid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'subjectid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'subjectid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'subjectid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'classid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'classid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'subjectid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'subjectid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'classid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'classid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'subjectid' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'subjectid' 无效。

Answer 4

--生成测试数据
set nocount on
create table sc(stuid int,subjectid int,score float)
insert into sc select 1,1,90
insert into sc select 1,2,80
insert into sc select 1,3,80
insert into sc select 1,4,100
insert into sc select 1,5,99
insert into sc select 2,6,80
insert into sc select 2,1,80
insert into sc select 2,2,99
insert into sc select 2,3,80
insert into sc select 2,4,80
insert into sc select 2,5,80
insert into sc select 2,6,80 
insert into sc select 3,1,80
insert into sc select 3,2,80
insert into sc select 3,3,97
insert into sc select 3,4,80
insert into sc select 3,5,80
insert into sc select 3,6,80
insert into sc select 4,1,80
insert into sc select 4,2,80
insert into sc select 4,3,80
insert into sc select 4,4,80
insert into sc select 4,5,80
insert into sc select 4,6,96
insert into sc select 5,1,90
insert into sc select 5,2,90
insert into sc select 5,3,90
insert into sc select 5,4,90
insert into sc select 5,5,90
insert into sc select 5,6,80
insert into sc select 6,1,80
insert into sc select 6,2,80
insert into sc select 6,3,80
insert into sc select 6,4,80create table stu(stuid int,stuname varchar(10),classid int)
insert into stu select 1,'小白',1
insert into stu select 2,'张三',1
insert into stu select 3,'宋华',1
insert into stu select 4,'王五',1
insert into stu select 5,'赵善',2
insert into stu select 6,'李四',2create table subject(subjectid int,subjectname varchar(10))
insert into subject select 1,'语文'
insert into subject select 2,'数学'
insert into subject select 3,'英语'
insert into subject select 4,'物理'
insert into subject select 5,'化学'
insert into subject select 6,'生物'set nocount offdeclare @sql varchar (8000)select @sql='select cast(a.classid as varchar)+''班'' 班级'
select 
    @sql=@sql+',['+a.subjectname+']= max(case subjectid when '+rtrim(a.subjectid)+' then stuname end)+''(''
                                    +rtrim(max(case subjectid when '+rtrim(a.subjectid)+' then score end))+'')'''
from 
    (select distinct c.* from sc b,subject c where c.subjectid=b.subjectid) a
order by a.subjectidset @sql=@sql+' from
    (select  
         b.stuname,b.classid,c.*
     from
         stu b,sc c
     where
         b.stuid=c.stuid
         and
         not exists(select 
                        1 
                    from 
                        stu d,sc e 
                    where 
                        d.stuid=e.stuid 
                        and 
                        d.classid=b.classid 
                        and 
                        e.subjectid=c.subjectid 
                        and 
                        e.score>c.score)) a group by a.classid'exec(@sql)
go
/*
班级    语文       数学       英语       物理       化学       生物       
------  ---------  ---------  ---------  ---------  ---------  ---------
1班     小白(90)   张三(99)   宋华(97)   小白(100)  小白(99)   王五(96)
2班     赵善(90)   赵善(90)   赵善(90)   赵善(90)   赵善(90)   赵善(80)
*/drop table sc,stu,subject

Answer 5

呵呵declare @sql varchar (8000)
select @sql='select cast(b.classid as varchar)+''班'' 班级'
select 
    @sql=@sql+',['+a.subjectname+'] =cast( max(case c.subjectid when '+rtrim(a.subjectid) +' then b.stuname else null end) as varchar)+''(''+cast( max(case c.subjectid when '+rtrim(a.subjectid) +' then c.score else 0 end) as varchar)+'')'''
from 
    (select distinct c.* from sc b,subject c where c.subjectid=b.subjectid) a
order by a.subjectid
set @sql=@sql+' from stu b,sc c where b.stuid=c.stuid and not exists ( select 1 from stu b1,sc c1 where b1.stuid=c1.stuid and c1.classid=c.classid and b1.subjectid=b.subjectid and c1.score>c.score) group by b.classid'
print convert(varchar(8000),@sql)
exec(@sql)

Answer 6

use tempdb
go--生成测试数据
set nocount on
create table sc(stuid int,subjectid int,score float)
insert into sc select 1,1,90
insert into sc select 1,2,80
insert into sc select 1,3,80
insert into sc select 1,4,100
insert into sc select 1,5,99
insert into sc select 2,6,80
insert into sc select 2,1,80
insert into sc select 2,2,99
insert into sc select 2,3,80
insert into sc select 2,4,80
insert into sc select 2,5,80
insert into sc select 2,6,80 
insert into sc select 3,1,80
insert into sc select 3,2,80
insert into sc select 3,3,97
insert into sc select 3,4,80
insert into sc select 3,5,80
insert into sc select 3,6,80
insert into sc select 4,1,80
insert into sc select 4,2,80
insert into sc select 4,3,80
insert into sc select 4,4,80
insert into sc select 4,5,80
insert into sc select 4,6,96
insert into sc select 5,1,90
insert into sc select 5,2,90
insert into sc select 5,3,90
insert into sc select 5,4,90
insert into sc select 5,5,90
insert into sc select 5,6,80
insert into sc select 6,1,80
insert into sc select 6,2,80
insert into sc select 6,3,80
insert into sc select 6,4,80create table stu(stuid int,stuname varchar(10),classid int)
insert into stu select 1,'小白',1
insert into stu select 2,'张三',1
insert into stu select 3,'宋华',1
insert into stu select 4,'王五',1
insert into stu select 5,'赵善',2
insert into stu select 6,'李四',2create table subject(subjectid int,subjectname varchar(10))
insert into subject select 1,'语文'
insert into subject select 2,'数学'
insert into subject select 3,'英语'
insert into subject select 4,'物理'
insert into subject select 5,'化学'
insert into subject select 6,'生物'godeclare @sql varchar (8000)
select @sql='select cast(a.classid as varchar)+''班'' 班级'
select 
    @sql=@sql+',['+a.subjectname+'] =max(case a.subjectid when '+rtrim(a.subjectid) +' then name else '''' end)'
from 
    (select distinct c.* from sc b,subject c where c.subjectid=b.subjectid) a
order by a.subjectid
set @sql=@sql+' 
from(
select stu.classid, name = stu.stuname+quotename(sc.score, ''()''), sub.subjectname, sub.subjectid
from sc, stu, subject sub
where sc.stuid = stu.stuid
    and sc.subjectid = sub.subjectid
    and not exists(
     select * from sc sc1, stu stu1
     where sc1.stuid = stu1.stuid
        and sc1.score > sc.score
        and sc1.subjectid = sc.subjectid 
        and stu1.classid = stu.classid)
)a group by classid'
print convert(varchar(8000),@sql)
exec(@sql)godrop table sc, stu,subject-- 结果
班级    语文        数学         英语         物理         化学         生物     
------- ---------- ------------ --- -------- ------------ ----------- ---------
1班    小白(90)     张三(99)     宋华(97)     小白(100)    小白(99)     王五(96)
2班    赵善(90)     赵善(90)     赵善(90)     赵善(90)     赵善(90)     赵善(80)

Answer 7

楼上的老大,如果我在统计表的最后还想加一行(是对于年级的统计)
这又需要对代码做些什么改动
是不是在最后加上union前面的就成

Answer 8

--生成测试数据
set nocount on
create table sc(stuid int,subjectid int,score float)
insert into sc select 1,1,90
insert into sc select 1,2,80
insert into sc select 1,3,80
insert into sc select 1,4,100
insert into sc select 1,5,99
insert into sc select 2,6,80
insert into sc select 2,1,80
insert into sc select 2,2,99
insert into sc select 2,3,80
insert into sc select 2,4,80
insert into sc select 2,5,80
insert into sc select 2,6,80 
insert into sc select 3,1,80
insert into sc select 3,2,80
insert into sc select 3,3,97
insert into sc select 3,4,80
insert into sc select 3,5,80
insert into sc select 3,6,80
insert into sc select 4,1,80
insert into sc select 4,2,80
insert into sc select 4,3,80
insert into sc select 4,4,80
insert into sc select 4,5,80
insert into sc select 4,6,96
insert into sc select 5,1,90
insert into sc select 5,2,90
insert into sc select 5,3,90
insert into sc select 5,4,90
insert into sc select 5,5,90
insert into sc select 5,6,80
insert into sc select 6,1,80
insert into sc select 6,2,80
insert into sc select 6,3,80
insert into sc select 6,4,80create table stu(stuid int,stuname varchar(10),classid int)
insert into stu select 1,'小白',1
insert into stu select 2,'张三',1
insert into stu select 3,'宋华',1
insert into stu select 4,'王五',1
insert into stu select 5,'赵善',2
insert into stu select 6,'李四',2create table subject(subjectid int,subjectname varchar(10))
insert into subject select 1,'语文'
insert into subject select 2,'数学'
insert into subject select 3,'英语'
insert into subject select 4,'物理'
insert into subject select 5,'化学'
insert into subject select 6,'生物'set nocount offdeclare @sql varchar (8000)select @sql=''
select 
    @sql=@sql+',['+a.subjectname+']= max(case subjectid when '+rtrim(a.subjectid)+' then stuname end)+''(''
                                    +rtrim(max(case subjectid when '+rtrim(a.subjectid)+' then score end))+'')'''
from 
    (select distinct c.* from sc b,subject c where c.subjectid=b.subjectid) a
order by a.subjectidset @sql= 'select cast(a.classid as varchar)+''班'' 班级'+@sql+' from (select b.stuname,b.classid,c.* from stu b,sc c where b.stuid=c.stuid and not exists(select 1 from stu d,sc e where d.stuid=e.stuid and d.classid=b.classid and e.subjectid=c.subjectid and e.score>c.score)) a group by a.classid'
         +' union select ''年级'''+@sql+' from (select b.stuname,b.classid,c.* from stu b,sc c where b.stuid=c.stuid and not exists(select 1 from stu d,sc e where d.stuid=e.stuid and e.subjectid=c.subjectid and e.score>c.score)) a'exec(@sql)
go
/*
班级    语文       数学       英语       物理       化学       生物       
------  ---------  ---------  ---------  ---------  ---------  ---------
1班     小白(90)   张三(99)   宋华(97)   小白(100)  小白(99)   王五(96)
2班     赵善(90)   赵善(90)   赵善(90)   赵善(90)   赵善(90)   赵善(80)
年级    赵善(90)   张三(99)   宋华(97)   小白(100)  小白(99)   王五(96)*/drop table sc,stu,subject