--按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績
select s_id as '學號',
sum(case c_id when '01' then s_score else 0 end) as '語文',
sum(case c_id when '02' then s_score else 0 end) as '數學',
sum(case c_id when '03' then s_score else 0 end) as '英語',
avg(s_score) as '平均成績'
from score
group by s_id
order by '平均成績' desc;
學號 語文 數學 英語 平均成績
07 0 89 98 93
01 80 90 99 89
05 76 87 0 81
03 80 80 80 80
02 70 60 80 70
04 50 30 20 33
06 31 0 34 32
uj5u.com熱心網友回復：
select 學號,語文,數學,英語,(語文+數學+英語)/3 as 平均成績
from (
select s_id as '學號',
sum(case c_id when '01' then s_score else 0 end) as '語文',
sum(case c_id when '02' then s_score else 0 end) as '數學',
sum(case c_id when '03' then s_score else 0 end) as '英語'
from score
group by s_id
) as t
order by 平均成績 desc;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/127318.html
標籤：疑難問題
上一篇：sql布爾盲注
下一篇：迷茫的大四狗