原来的问题如:
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)
insert into a values ( 4,7,50)
insert into a values ( 4,8,40)
insert into a values ( 4,9,60)
概是我描述不清楚 ,其实很简单,只是要大家修改下
首先大家 不看第二个排序表
我只对第一个表进行排名
select sname,
[4月]=sum(case when smonth=7 then score else 0 end),
[5月]=sum(case when smonth=8 then score else 0 end),
[6月]=sum(case when smonth=9 then score else 0 end),
[总分]=sum(score),
[排名]=( select count(*)+1 from(
select col=1 from a where smonth in(7,8,9) group by sname having sum(score)>sum(tmpA.score)
) tmp
)
from a tmpA
where smonth in(7,8,9)
group by sname
代码是正确的
我想在这个结果上再对sname 进行排序 ,对sname排序的对于关系就是 排序表.sname=a.sname
要求的结果是:
/*
结果:
sname 7月 8月 9月 总分 排名
------------------------------------------
1 80 40 80 200 1
3 50 80 60 190 2
4 50 40 60 150 3
2 50 40 60 150 4
*/
参考答案:
使用到临时表就可以 了。如:
SELECT A.*,B.总分,IDENTITY(int,1,1) AS [排名] INTO #T FROM (
SELECT sname
,SUM(CASE smonth WHEN 7 THEN score ELSE 0 END) AS '7月'
,SUM(CASE smonth WHEN 8 THEN score ELSE 0 END) AS '8月'
,SUM(CASE smonth WHEN 9 THEN score ELSE 0 END) AS '9月'
FROM a GROUP BY sname
) AS A
RIGHT OUTER JOIN
(SELECT TOP 100 PERCENT sname,SUM(score) AS 总分 FROM a GROUP BY sname ORDER BY SUM(score) DESC) AS B
ON B.sname=A.sname
SELECT * FROM #T
DROP TABLE #T
来源:http://community.csdn.net/Expert/topic/5347/5347344.xml?temp=4.992312E-02
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7764484/viewspace-898660/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7764484/viewspace-898660/