求一条排名的SQL
表A
name month score
1 7 80
1 8 40
1 9 80
2 7 50
2 8 40
2960
得到表 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(score) as '总分 '
into test
FROM tb
GROUP BY name
--result
name 1月份 2月份 3月份 4月份 5月份 6月份 7月份 8月份 9月份 10月份 11月份 12月份 总分 排名
---- ----- ----- ----- ----- ----- ----- ----- ----- ----- ------ ------ ------ ---- ----
1 0 0 0 0 0 0 80 40 80 0 0 0 200 1
2 0 0 0 0 0 0 50 40 60 0 0 0 150 2
（所影响的行数为 2 行）
select * , 排名 = (select count(1) from test where 总分> t.总分) + 1 from test t
drop table tb,test
[解决办法]
select
name,
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(score) as [总分]
from (
select 1 as [name],7 as [month], 80 as [score]
union all select 1,8,40
union all select 1,8,80
union all select 2,9,50
union all select 2,8,40
union all select 2,8,60) t
group by name
order by [总分] desc
不知道这个算不算事一条语句？
