102.交叉表-复杂名次

--成绩统计示例

--测试表
create table #t(xh varchar(3),xm varchar(10),km varchar(10),cj int)
insert into #t
select '001','张三','语文',80
union all select '001','张三','数学',85
union all select '002','李四','语文',90
union all select '002','李四','数学',80
union all select '003','王五','语文',70
union all select '003','王五','数学',78

--数据处理
declare @sql nvarchar(4000),@sql1 nvarchar(4000)
select @sql='',@sql1=''
select @sql=@sql+',['+km
	+']=sum(case km when '''+km+''' then cj else 0 end)'
	,@sql1=@sql1+',['+km+'名次]=(select sum(1) from # where ['
	+km+']>=a.['+km+'])'
from(select distinct km from #t) a
exec('select xh 学号,xm 姓名'+@sql+',总成绩=sum(cj)
,总名次=(select sum(1) from(select xh,aa=sum(cj) from #t group by xh) aa where sum(a.cj)<=aa)
into # from #t a group by xh,xm
select *'+@sql1+' from # a
')

drop table #t

/*--测试结果

学号  姓名   数学    语文     总成绩      总名次       数学名次    语文名次        
---- ------ ------- -------- ----------- ----------- ----------- ----------- 
002  李四   80      90       170         1           2           1
003  王五   78      70       148         3           3           3
001  张三   85      80       165         2           1           2

--*/