求一条排名的SQL
表A
  name         month           score
  1                 7                   80
1                 8                     40
  1             9                       80

2             7                         50
2           8                         40  
2 9 60  


得到表   B
            7   月分                 8                   9         总分     排名
1        
2
最要命的是排名         不知道什么写
求一条SQL

------解决方案--------------------
先生成一個臨時表, 再排名可以嗎？
------解决方案--------------------
排名用个子查询,查询比自己分数高的记录就可以了.
create table a
(sname int,
smonth int,
score int)

insert into a values ( 1,7,80)
insert into a values ( 1,8,40)
insert into a values ( 1,9,80)
insert into a values ( 2,7,50)
insert into a values ( 2,8,40)
insert into a values ( 2,9,60)
insert into a values ( 3,7,50)
insert into a values ( 3,8,80)
insert into a values ( 3,9,60)
select sname,sum(score*(case when smonth=7 then 1 else 0 end)) [7月],
sum(score*(case when smonth=8 then 1 else 0 end)) [8月],sum(score*(case when smonth=9 then 1 else 0 end)) [9月],
sum(score) 总分,(select count(*)+1 from (select 'a ' a from a group by sname having sum(score)> (select sum(score) s from a i where i.sname=o.sname) )t ) 排名
from a o
group by o.sname

------解决方案--------------------
if object_id( 'pubs..tb ') is not null
drop table tb
go

create table tb
(
name varchar(10),
month int,
score int
)

insert into tb(name,month,score) values( '1 ',7,80)
insert into tb(name,month,score) values( '1 ',8,40)
insert into tb(name,month,score) values( '1 ',9,80)
insert into tb(name,month,score) values( '2 ',7,50)
insert into tb(name,month,score) values( '2 ',8,40 )
insert into tb(name,month,score) values( '2 ',9,60 )

SELECT name ,
SUM(CASE month WHEN 1 THEN score ELSE 0 END) AS '1月份 ' ,
SUM(CASE month WHEN 2 THEN score ELSE 0 END) AS '2月份 ' ,
SUM(CASE month WHEN 3 THEN score ELSE 0 END) AS '3月份 ' ,
SUM(CASE month WHEN 4 THEN score ELSE 0 END) AS '4月份 ' ,
SUM(CASE month WHEN 5 THEN score ELSE 0 END) AS '5月份 ' ,
SUM(CASE month WHEN 6 THEN score ELSE 0 END) AS '6月份 ' ,
SUM(CASE month WHEN 7 THEN score ELSE 0 END) AS '7月份 ' ,
SUM(CASE month WHEN 8 THEN score ELSE 0 END) AS '8月份 ' ,
SUM(CASE month WHEN 9 THEN score ELSE 0 END) AS '9月份 ' ,
SUM(CASE month WHEN 10 THEN score ELSE 0 END) AS '10月份 ' ,
SUM(CASE month WHEN 11 THEN score ELSE 0 END) AS '11月份 ' ,
SUM(CASE month WHEN 12 THEN score ELSE 0 END) AS '12月份 ' ,
SUM(s