oracle/mysql语句查询结果列转行

select * from tablename数据查询出来如下所示:

key name address nums
1 iPhone China 1000
2 Samsung China 880
3 iPhone Japan 900
4 iPhone USA 3000
5 Samsung USA 2000

想将name列作为行展示,如下:

name   China Japan USA
iPhone   1000 900 3000
Samsung   880 0 2000
oracle语句为:
select name ,
sum(decode(address,'China',nums,0)) as China,
sum(decode(address,'Japan',nums,0)) as Japan,
sum(decode(address,'USA',nums,0)) as USA
from tablename
group by name

mysql语句为:
SELECT name ,
sum(CASE address WHEN 'China' THEN nums ELSE 0 END ) China,
sum(CASE address WHEN 'Japan' THEN nums ELSE 0 END ) Japan,
sum(CASE address WHEN 'USA' THEN nums ELSE 0 END ) USA
FROM tablename
GROUP BY name


发表评论

电子邮件地址不会被公开。