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
;