sql行列转换_SQL试题六 行列转换

行列转换

appchannel	user_cnt
360	17
xiaomi	15
AppStore	20
vivo	31

如何写一个SQL，输出如下格式的数据：

360	xiaomi	AppStore	vivo
17	15	20	31

方法一 ：union all

第一步

SELECT user_cnt as '360',0 as 'xiaomi',0 as 'appstore', 0  as 'vivo' 
FROM six_app WHERE appchannel = "360";

SELECT 0 as '360',user_cnt as 'xiaomi',0 as 'appstore', 0  as 'vivo' 
FROM six_app WHERE appchannel = "xiaomi";

SELECT 0 as '360',0 as 'xiaomi',user_cnt as 'appstore', 0  as 'vivo' 
FROM six_app WHERE appchannel = "appstore";

SELECT 0 as '360',0 as 'xiaomi',0 as 'appstore', user_cnt as 'vivo' 
FROM six_app WHERE appchannel = "vivo";

union all 连接，有重复使用all，先查询所有

SELECT * 
FROM(
SELECT user_cnt as '360',0 as 'xiaomi',0 as 'appstore', 0  as 'vivo' FROM six_app WHERE appchannel = "360"
UNION ALL
SELECT 0 as '360',user_cnt as 'xiaomi',0 as 'appstore', 0  as 'vivo' FROM six_app WHERE appchannel = "xiaomi"
UNION ALL
SELECT 0 as '360',0 as 'xiaomi',user_cnt as 'appstore', 0  as 'vivo' FROM six_app WHERE appchannel = "appstore"
UNION ALL
SELECT 0 as '360',0 as 'xiaomi',0 as 'appstore', user_cnt as 'vivo' FROM six_app WHERE appchannel = "vivo"
)tmp
;

使用sum求和

SELECT SUM(tmp.360) '360',SUM(tmp.xiaomi) 'xiaomi',SUM(tmp.appstore)  'appstore',SUM(tmp.vivo) 'vivo'
FROM(
SELECT user_cnt as '360',0 as 'xiaomi',0 as 'appstore', 0  as 'vivo' FROM six_app WHERE appchannel = "360"
UNION ALL
SELECT 0 as '360',user_cnt as 'xiaomi',0 as 'appstore', 0  as 'vivo' FROM six_app WHERE appchannel = "xiaomi"
UNION ALL
SELECT 0 as '360',0 as 'xiaomi',user_cnt as 'appstore', 0  as 'vivo' FROM six_app WHERE appchannel = "appstore"
UNION ALL
SELECT 0 as '360',0 as 'xiaomi',0 as 'appstore', user_cnt as 'vivo' FROM six_app WHERE appchannel = "vivo"
)tmp
;

---

方法二 join

下面使用join方法

SELECT appchannel,user_cnt FROM six_app WHERE appchannel='360';

SELECT appchannel,user_cnt FROM six_app WHERE appchannel='xiaomi';

SELECT appchannel,user_cnt FROM six_app WHERE appchannel='appstore';

SELECT appchannel,user_cnt FROM six_app WHERE appchannel='vivo';

查询所有

SELECT * 
FROM
(SELECT user_cnt FROM six_app WHERE appchannel='360')t1
JOIN
(SELECT user_cnt FROM six_app WHERE appchannel='xiaomi')t2
JOIN
(SELECT user_cnt FROM six_app WHERE appchannel='appstore')t3
JOIN
(SELECT user_cnt FROM six_app WHERE appchannel='vivo')t4
;

结果

SELECT t1.user_cnt '360',t2.user_cnt 'xiaomi',t3.user_cnt 'appstore',t4.user_cnt 'vivo'
FROM
(SELECT user_cnt FROM six_app WHERE appchannel='360')t1
JOIN
(SELECT user_cnt FROM six_app WHERE appchannel='xiaomi')t2
JOIN
(SELECT user_cnt FROM six_app WHERE appchannel='appstore')t3
JOIN
(SELECT user_cnt FROM six_app WHERE appchannel='vivo')t4
;