mysql查询结果横表，纵表数据转换

1. 横表 --> 纵表

union all
https://blog.51cto.com/u_16175507/9832364
https://blog.51cto.com/u_16175522/9518511

2. 纵表 --> 横表

https://blog.csdn.net/qq_46414039/article/details/132120660

纵表数据

-- 纵表数据
SELECTb.app_id AS app_id,b.env AS env,COUNT(b.id) AS COUNTFROM assembler.app_version b
INNER JOIN app a ON a.is_delete =0 AND a.id = b.app_id
WHERE b.is_delete =0
/**/
AND b.app_id IN
(1640596419670708225,1684372201690222593                        
)GROUP BY b.`app_id`,b.env

横表数据

-- 横表数据
SELECTaa.`app_id`,SUM(CASE aa.env WHEN 'sit' THEN aa.count ELSE 0 END) AS 'sit',SUM(CASE aa.env WHEN 'uat' THEN aa.count ELSE 0 END) AS 'uat',SUM(CASE aa.env WHEN 'pre' THEN aa.count ELSE 0 END) AS 'pre',SUM(CASE aa.env WHEN 'prod' THEN aa.count ELSE 0 END) AS 'prod'FROM 
(SELECTb.app_id AS app_id,b.env AS env,COUNT(b.id) AS COUNTFROM assembler.app_version bINNER JOIN app a ON a.is_delete =0 AND a.id = b.app_idWHERE b.is_delete =0/**/AND b.app_id IN(1640596419670708225,1684372201690222593                        )GROUP BY b.`app_id`,b.env) AS aa        
GROUP BY aa.`app_id`