![aaef10de4a96fa34d7a3ad6d7d44157e.png](https://i-blog.csdnimg.cn/blog_migrate/ccdecbe51e30b7ebe15c75b8a1cafff4.png)
行列转换
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";
![438570a8332953d6c99d641bbcf26e9a.png](https://i-blog.csdnimg.cn/blog_migrate/c4edb6eb65f6095586b264dce8b26804.png)
SELECT 0 as '360',user_cnt as 'xiaomi',0 as 'appstore', 0 as 'vivo'
FROM six_app WHERE appchannel = "xiaomi";
![95307001c2f23302a155e88f0bd96fc2.png](https://i-blog.csdnimg.cn/blog_migrate/4fef321384d0ab7f82e625a3ee9d3708.png)
SELECT 0 as '360',0 as 'xiaomi',user_cnt as 'appstore', 0 as 'vivo'
FROM six_app WHERE appchannel = "appstore";
![5a4940499af19128cab4fbd911eef077.png](https://i-blog.csdnimg.cn/blog_migrate/a2864166de56cd91ce488d1eb0561b76.png)
SELECT 0 as '360',0 as 'xiaomi',0 as 'appstore', user_cnt as 'vivo'
FROM six_app WHERE appchannel = "vivo";
![7106f72bb68af222f245a42f19ecce1c.png](https://i-blog.csdnimg.cn/blog_migrate/3913e97fd8a909599e226b698f2ea32f.png)
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
;
![94ef58d55bf45bc2590d19766f92c1f6.png](https://i-blog.csdnimg.cn/blog_migrate/59cf1c37e372b49fad4e715de9b59feb.png)
使用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
;
![b84efde09c5d42d1320154a294908c5b.png](https://i-blog.csdnimg.cn/blog_migrate/de7add511992d26bdbb4613eeb5a8854.png)
方法二 join
下面使用join方法
SELECT appchannel,user_cnt FROM six_app WHERE appchannel='360';
![9cec10895e024493802d9dfffc16143a.png](https://i-blog.csdnimg.cn/blog_migrate/5cf5a4122ce8ad510d4f2acdeea60468.png)
SELECT appchannel,user_cnt FROM six_app WHERE appchannel='xiaomi';
![1bcb17d0ab4a719f6a77ab171c0a3ec7.png](https://i-blog.csdnimg.cn/blog_migrate/abd57e0568e5d66ca27574357edef3ee.png)
SELECT appchannel,user_cnt FROM six_app WHERE appchannel='appstore';
![63185a6007800b4d1c3032c4f43dc65c.png](https://i-blog.csdnimg.cn/blog_migrate/995bb7e7050c0de3c05abdd32ef6e563.png)
SELECT appchannel,user_cnt FROM six_app WHERE appchannel='vivo';
![5ec7f783d3f67c6fc101ae6cdbe2b419.png](https://i-blog.csdnimg.cn/blog_migrate/7bdeb5c3e819d44b2dada60d24adabcf.png)
查询所有
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
;
![575771f3d786e20b5d574b8760ba7954.png](https://i-blog.csdnimg.cn/blog_migrate/39b64e6e3721bf697cdcbcc72326d106.png)
结果
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
;
![9ae06cff10186d0f6794280dfdf7572d.png](https://i-blog.csdnimg.cn/blog_migrate/64f1630ba07f35cfdb75b78ecba26413.png)