insert dduser([month],username,userDisplay,rank,score) values( 7 ,N'Demon__Hunter' , N' 党(."".)主席【呆呆】' ,7,2692)

insert dduser([month],username,userDisplay,rank,score) values( 7 ,N'紫贝壳' , N' 党(."".)紫贝壳' ,8,2649)

insert dduser([month],username,userDisplay,rank,score) values( 7 ,N'zhangxinbin5' , N' 党(."".)彬-董事长' ,11,2378)

insert dduser([month],username,userDisplay,rank,score) values( 7 ,N'SCAUSCNU' , N' 党(."".)阳光女孩' ,19,1571)

insert dduser([month],username,userDisplay,rank,score) values( 7 ,N'阿呆哥' , N' 党(."".)阿呆哥' ,39,752)

insert dduser([month],username,userDisplay,rank,score) values( 7 ,N'naonaoye' , N' 党(."".)小坑' ,44,699)

insert dduser([month],username,userDisplay,rank,score) values( 7 ,N'mni2005' , N' 党(."".)总裁【呆呆】' ,56,575)

insert dduser([month],username,userDisplay,rank,score) values( 7 ,N'star_jerry90' , N' 党(."".)Jerry' , 95,378) www.2cto.com

insert dduser([month],username,userDisplay,rank,score) values( 8 ,N'tan598121925' , N' 党(."".) Jian.' ,4,3842)

insert dduser([month],username,userDisplay,rank,score) values( 8 ,N'xp447196763' , N' 党(."".)小 ' ,5,3235 )

insert dduser([month],username,userDisplay,rank,score) values( 8 ,N'zhangxinbin5' , N' 党(."".)彬-董事长' ,7,2858 )

insert dduser([month],username,userDisplay,rank,score) values( 8 ,N'renkuan719' , N' 党(."".)一缕风' ,9,2517 )

insert dduser([month],username,userDisplay,rank,score) values( 8 ,N'SCAUSCNU' , N' 党(."".)阳光女孩' ,12,2043 )

insert dduser([month],username,userDisplay,rank,score) values( 8 ,N'q2920' , N' 党(."".)初念' ,23,1183 )

insert dduser([month],username,userDisplay,rank,score) values( 8 ,N'阿呆哥' , N' 党(."".)阿呆哥' ,37,660 )

insert dduser([month],username,userDisplay,rank,score) values( 8 ,N'Demon__Hunter' , N' 党(."".)主席【呆呆】' ,41,611 )

insert dduser([month],username,userDisplay,rank,score) values( 8 ,N'thesnowisflying' , N' 党(."".)钱袋袋' ,61,499 )

insert dduser([month],username,userDisplay,rank,score) values( 8 ,N'xuelang1225' , N' 党(."".)雪狼' ,70,440 )

insert dduser([month],username,userDisplay,rank,score) values( 8 ,N'q465897859' , N' 党(."".)--渐行渐远' , 87 ,354)

[sql] www.2cto.com

--SQL SERVER 2005+ 静态行转列示例

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 www.2cto.com

(select * from dduser) a

pivot (max(rank) for [month] in ([1],[2],[3],[4],[5],[6],[7],[8])) b