SQL表行列转换后再计算排名

行列转换后再加一个总分列,名次列

select st.StuNo, name as 姓名 ,
max(case course when '语文' then score else 0 end) 语文,
max(case course when '数学' then score else 0 end) 数学,
max(case course when '物理' then score else 0 end) 物理,
sum(score) as 总分,
RANK() OVER(ORDER BY sum(score)desc)  as 名次
from Student as st,Score as sc
where st.StuName=sc.name
group by name,st.StuNo
order by 总分 desc

用RANK() 会出现相同名次的情况

如：

成绩	名次
92	1
92	1
90	3

而使用ROW_NUMBER() 则不会出现这种情况

代码如下：

select st.StuNo, name as 姓名 ,
max(case course when '语文' then score else 0 end) 语文,
max(case course when '数学' then score else 0 end) 数学,
max(case course when '物理' then score else 0 end) 物理,
sum(score) as 总分,
ROW_NUMBER() OVER(ORDER BY sum(score)desc)  as 名次
from Student as st,Score as sc
where st.StuName=sc.name
group by name,st.StuNo
order by 总分 desc

如：

成绩	名次
92	1
92	2
90	3

 

转载于:https://blog.51cto.com/tangchaolizi/1028638