SQL高手来帮忙！

[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))

我有如下的查询语句
declare @sql varchar (8000),@sql1 varchar (8000)
select @sql='',@sql1=''
select @sql=@sql+',['+subjectname+']=sum(case subjectid when '''+cast(subjectid as varchar(100)) +'''
then score else 0 end)' from(select distinct b.subjectid,c.subjectname
from sc as b inner join subject as c on c.subjectid=b.subjectid) as a order by subjectid

Exec('select 班级=(select classid from stu where stuid=a.stuid)'+@sql+' into # from sc as a group by stuid
select 班级,与考人数=(sum(case 班级 when 班级 then 1 else 0 end))
,Convert(dec(5,1),avg(语文)) 语文平均分
,cast(sum(case when 语文>=90 then 1.0 else 0 end)*100/sum(case 班级 when 班级 then 1 else 0 end) as numeric(5,2)) 语文优秀率
,cast(sum(case when 语文>=60 then 1.0 else 0 end)*100/sum(case 班级 when 班级 then 1 else 0 end) as numeric(5,2)) 语文及格率
,max(语文) 语文最高分
from # group by 班级')

上面的查询我只能得到一科及对语文的统计，我想达到的效果为在@sql中得到有几科，对其的统计也就需要有几科，这主要又是个动态的问题，所以对我这种菜鸟来说有点难
高手们请帮忙啊
把你写的代码共享一下，高分回报（呵呵，至少有100分）

[-狙击手-]

要下班了，要是明天没有人回，我再试试看

[splory]

好的

我现在有个循环可以实现但不知道怎么加进去阿

你们也可以看看
select IDENTITY(smallint, 100, 1) AS lsh,name,0 as flag into #temp_col from syscolumns where id in
(
select id from sysobjects where name='#'
)
and name not in('班级')


DECLARE @name VARCHAR(200),
@lsh smallint,
@str varchar(1000)
--select * from #temp_col
while exists(select top 1 * from #temp_col where flag=0)
begin
SELECT top 1 @lsh=lsh,@name=name from #temp_col where flag=0 order by lsh
update #temp_col set flag=1 where lsh=@lsh

end

[子陌红尘]

create table sc(stuid int,subjectid int,score float)
insert into sc select 1,1,90
insert into sc select 2,1,80
insert into sc select 3,1,70
insert into sc select 4,1,60
insert into sc select 5,1,50
insert into sc select 6,1,40
insert into sc select 1,2,80
insert into sc select 2,2,80
insert into sc select 3,2,70
insert into sc select 4,2,60
insert into sc select 5,2,50
insert into sc select 6,2,70

create table stu (stuid int,stuname varchar(10),classid int)
insert into stu select 1,'AAA',1
insert into stu select 2,'BBB',1
insert into stu select 3,'CCC',1
insert into stu select 4,'DDD',1
insert into stu select 5,'EEE',1
insert into stu select 6,'FFF',1

create table subject (subjectid int,subjectname varchar(10))
insert into subject select 1,'语文'
insert into subject select 2,'数学'

--我有如下的查询语句
declare @sql varchar (8000),@sql1 varchar (8000)
select @sql='select b.classid'
select
@sql=@sql+',['+a.subjectname+'与考人数]= sum(case c.subjectid when '+rtrim(a.subjectid) +' then 1 else 0 end)'
+',['+a.subjectname+'平均分] = sum(case c.subjectid when '+rtrim(a.subjectid) +' then c.score else 0 end)/
sum(case c.subjectid when '+rtrim(a.subjectid) +' then 1 else 0 end)'
+',['+a.subjectname+'优秀率] = rtrim(cast(sum(case when c.subjectid='+rtrim(a.subjectid) +' and c.score>=90 then 1 else 0 end)*100.0/
sum(case c.subjectid when '+rtrim(a.subjectid) +' then 1 else 0 end) as numeric(5,2)))+''%'''
+',['+a.subjectname+'及格率] = rtrim(cast(sum(case when c.subjectid='+rtrim(a.subjectid) +' and c.score>=60 then 1 else 0 end)*100.0/
sum(case c.subjectid when '+rtrim(a.subjectid) +' then 1 else 0 end) as numeric(5,2)))+''%'''
+',['+a.subjectname+'最高分] = max(case c.subjectid when '+rtrim(a.subjectid) +' then c.score end)'
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 group by b.classid'

exec(@sql)
go

drop table subject,stu,sc

[splory]

ok,了解了,只能说老大你很牛鼻!
谢谢啦,有问题再问你
结贴

[splory]

楼上的,你这样查询出来的与考人数由于在循环里,就多出现了几列阿
我的要求是只用一列,求总的与考人数就可以了
这又怎么搞
谢谢

[子陌红尘]

create table sc(stuid int,subjectid int,score float)
insert into sc select 1,1,90
insert into sc select 2,1,80
insert into sc select 3,1,70
insert into sc select 4,1,60
insert into sc select 5,1,50
insert into sc select 6,1,40
insert into sc select 1,2,80
insert into sc select 2,2,80
insert into sc select 3,2,70
insert into sc select 4,2,60
insert into sc select 5,2,50
insert into sc select 6,2,70

create table stu (stuid int,stuname varchar(10),classid int)
insert into stu select 1,'AAA',1
insert into stu select 2,'BBB',1
insert into stu select 3,'CCC',1
insert into stu select 4,'DDD',1
insert into stu select 5,'EEE',1
insert into stu select 6,'FFF',1

create table subject (subjectid int,subjectname varchar(10))
insert into subject select 1,'语文'
insert into subject select 2,'数学'

--我有如下的查询语句
declare @sql varchar (8000),@sql1 varchar (8000)
select @sql='select b.classid,[与考人数]=count(distinct b.stuid)'
select
@sql=@sql+',['+a.subjectname+'平均分] = sum(case c.subjectid when '+rtrim(a.subjectid) +' then c.score else 0 end)/
sum(case c.subjectid when '+rtrim(a.subjectid) +' then 1 else 0 end)'
+',['+a.subjectname+'优秀率] = rtrim(cast(sum(case when c.subjectid='+rtrim(a.subjectid) +' and c.score>=90 then 1 else 0 end)*100.0/
sum(case c.subjectid when '+rtrim(a.subjectid) +' then 1 else 0 end) as numeric(5,2)))+''%'''
+',['+a.subjectname+'及格率] = rtrim(cast(sum(case when c.subjectid='+rtrim(a.subjectid) +' and c.score>=60 then 1 else 0 end)*100.0/
sum(case c.subjectid when '+rtrim(a.subjectid) +' then 1 else 0 end) as numeric(5,2)))+''%'''
+',['+a.subjectname+'最高分] = max(case c.subjectid when '+rtrim(a.subjectid) +' then c.score end)'
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 group by b.classid'

exec(@sql)
go

drop table subject,stu,sc

[子陌红尘]

如果不需要过滤重复的stuid，将count(distinct b.stuid)里的distinct去掉。