001、mysql行转列，列转行

悟空编程2016

于 2024-09-13 17:05:36 发布

阅读量350

点赞数 14

分类专栏： # 2、数据库文章标签： mysql android 数据库

本文链接：https://blog.csdn.net/king27001/article/details/142215333

版权

2、数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

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
在这里插入图片描述

纵表数据

-- 纵表数据
SELECT
        
    b.app_id AS app_id,
    b.env AS env,
    COUNT(b.id) AS COUNT
    
FROM 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

在这里插入图片描述

横表数据

-- 横表数据
SELECT

        aa.`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 
(

        SELECT
        
            b.app_id AS app_id,
            b.env AS env,
            COUNT(b.id) AS COUNT
            
        FROM 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
        
) AS aa        
GROUP BY aa.`app_id`