行列转换后再加一个总分列,名次列
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 |