学步园

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 账号,
    max(isnull('第'+ltrim([1])+'名','')) as [1月],
    max(isnull('第'+ltrim([2])+'名','')) as [2月],
    max(isnull('第'+ltrim([3])+'名','')) as [3月],
    max(isnull('第'+ltrim([4])+'名','')) as [4月],
    max(isnull('第'+ltrim([5])+'名','')) as [5月],
    max(isnull('第'+ltrim([6])+'名','')) as [6月],
    max(isnull('第'+ltrim([7])+'名','')) as [7月],
    max(isnull('第'+ltrim([8])+'名','')) as [8月]
from 
(select * from dduser) a 
pivot (max(rank) for [month] in ([1],[2],[3],[4],[5],[6],[7],[8])) b
group by username,userDisplay 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 (
'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格式即可存储多个年限的了。