求一条sqlwith t as (select zhangsan stu, math sub, 70 scorefrom dualunion allselect zhangsan
求一条sql
with t as
(select 'zhangsan' stu, 'math' sub, 70 score
from dual
union all
select 'zhangsan' stu, 'english' sub, 70 score
from dual
union all
select 'zhangsan' stu, 'chinese' sub, 70 score
from dual
union all
select 'lisi' stu, 'chinese' sub, 70 score
from dual
union all
select 'lisi' stu, 'math' sub, 70 score
from dual
union all
select 'lisi' stu, 'english' sub, 70 score
from dual
union all
select 'wangwu' stu, 'english' sub, 70 score
from dual
union all
select 'wangwu' stu, 'math' sub, 70 score
from dual
union all
select 'wangwu' stu, 'chinese' sub, 70 score from dual)
select stu,
sum(decode(sub, 'chinese', score, null)) chinese,
sum(decode(sub, 'math', score, null)) math,
sum(decode(sub, 'english', score, null)) english
from t
group by t.stu