y order by 总得分 desc
--SQL SERVER 2005+ 动态行转列示例
declare @sql varchar(max),@colname varchar(max)
select @sql = isnull(@sql + '],[' , '') + ltrim([month]) from dduser group by [month]
set @sql = '[' + @sql + ']'
select @colname=
isnull(@colname+',','')+'max(isnull(''第''+ltrim(['
+ltrim([month])+'])+''名'','''')) as ['+ltrim([month])+'月]'
from dduser group by [month]
exec ( www.2cto.com
'select
row_number() over (order by
(select sum(score) from dduser
where username=b.username) desc) as 总排名,
(select sum(score) from dduser where username=b.username) as 总得分,
userDisplay as 昵称,
username as 账号,
'+@colname+'
from (select * from dduser) a
pivot (max(rank) for [month] in ('+@sql+')) b
group by username,userDisplay order by 总得分 desc')
--扩展：如果时间要扩展的话，把month字段改个名字，存储YYYY-MM格式即可存储多个年限的了。