方法:
--我们可以使用union all函数 将其凭借起来 select ( case id when 1 then 'first' when 2 then 'second' when 5 then 'fifth' end)grade ,sum( case subject when 'chinese' then score else 0 end) chinese ,sum( case subject when 'math' then score else 0 end) math ,sum( case subject when 'politics' then score else 0 end) politics from test group by id;
select * from ( select (case grade when 'first' then 1 when 'second' then 2 when 'fifth' then 5 end) id, 'chinese' subject, chinese as score from test2 union all select (case grade when 'first' then 1 when 'second' then 2 when 'fifth' then 5 end) id, 'math' subject, math as score from test2 union all select (case grade when 'first' then 1 when 'second' then 2 when 'fifth' then 5 end) id, 'politics' subject, politics as score from test2 order by id) a where a.score<>0;