declare @sql varchar(4000)
select @sql=',sum(case when SubName ='''+ SubName + ''' then Score else 0 end as ' + SubName from (select distinct SubName from SubTable) aaexec ('select StuCode,sum(Score) as Tatal '+ @sql + '
from GradeTable a ,SubTable b where a.subID=b.subID group by StuCode ')
select @sql=',sum(case when SubName ='''+ SubName + ''' then Score else 0 end as ' + SubName from (select distinct SubName from SubTable) aaexec ('select StuCode,sum(Score) as Tatal '+ @sql + '
from GradeTable a ,SubTable b where a.subID=b.subID group by StuCode ')
SET @SQL=''
SELECT @SQL=@SQL+',SUM(CASE WHEN SUBID='+CAST(SUBID AS VARCHAR(20))+' THEN SCORE ELSE 0 END) AS '+SUBNAME
FROM SUBTABLESET @SQL='SELECT StuCode'+@SQL +',SUM(SCORE) AS TOTAL FROM GRADETABLE GROUP BY StuCode'EXEC (@SQL)
left join subtable b on a.subid=b.subid
order by stucode,a.subid
SET @SQL=''
SELECT @SQL=@SQL+',SUM(CASE WHEN SUBID='+CAST(SUBID AS VARCHAR(20))+' THEN SCORE ELSE 0 END) AS '+SUBNAME
FROM SUBTABLE
SET @SQL='SELECT StuCode'+@SQL +',SUM(SCORE) AS TOTAL FROM GRADETABLE GROUP BY StuCode'
PRINT @SQL
EXEC (@SQL)
还有一个问题:
如果要显示出排名如下
StuCode English Math DS .... Tatal order
A 100 80 80 400 2
B 90 90 0 ..... 500 1
C...................................400 2
....
这个怎么查询实现哦!!!
update #temp set paiming=(select min(paiming) from 结果表 where 结果表.total=a.total group by total) from 结果表 a
select * from #temp
drop table #temp
update #temp set paiming=(select min(paiming) from 结果表 where 结果表.total=a.total group by total) from 结果表 a
select * from #temp
drop table #temp
update #temp
set paiming=(select min(paiming) from #temp a where a.total=b.total)
from #temp b
select * from #temp
drop table #temp
SET @SQL=''
SELECT @SQL=@SQL+',SUM(CASE WHEN SUBID='+CAST(SUBID AS VARCHAR(20))+' THEN SCORE ELSE 0 END) AS '+SUBNAME
FROM SUBTABLE
SET @SQL='SELECT StuCode'+@SQL +',SUM(SCORE) AS TOTAL INTO TTT FROM GRADETABLE GROUP BY StuCode'
PRINT @SQL
EXEC (@SQL)SELECT *,IDENTITY(INT,1,1) AS ORDER INTO #TTT FROM TTT ORDER BY TOTAL
DROP TABLE #TTT
DROP TABLE TTT
A 100 80 80 400 2
B 90 90 0 ..... 500 1
C...................................400 2可能要用到游标
SET @SQL=''
SELECT @SQL=@SQL+',SUM(CASE WHEN SUBID='+CAST(SUBID AS VARCHAR(20))+' THEN SCORE ELSE 0 END) AS '+SUBNAME
FROM SUBTABLE
SET @SQL='SELECT StuCode'+@SQL +',SUM(SCORE) AS TOTAL INTO TTT FROM GRADETABLE GROUP BY StuCode'
PRINT @SQL
EXEC (@SQL)SELECT AA.TOTAL,IDENTITY(INT,1,1) AS ORDER1 INTO #TTT FROM( SELECT DISTINCT TOTAL FROM TTT) AA ORDER BY TOTAL DESCSELECT A.*, B.ORDER1 FROM TTT A,#TTT B WHERE A.TOTAL=B.TOTALDROP TABLE #TTTDROP TABLE TTT我试了可以了