SQL如何实现数据表行转列、列转行？

当前位置：点晴教程→知识管理交流 →『技术文档交流』

admin

2024年2月1日 10:14 本文热度 528

SQL行转列、列转行可以帮助我们更方便地处理数据，生成需要的报表和结果集。本文将介绍在SQL中如何实现数据表地行转列、列转行操作，以及实际应用示例。

这里通过表下面三张表进行举例，建表语句和示例数据插入语句可以查看我之前的文章。

SQL创建数据库和数据表
数据分析师的日常，公众号：数据分析师的日常SQL创建数据库和数据表

数据表示例数据分别如下：

data_learning.product_order（商品销量表）：

data_learning.product（商品信息表）：

data_learning.product_category（商品二级分类信息表）：

行转列

1.1 使用CASE WHEN语句+聚合函数

问题：请查询女裙和儿童家具的销量，输出结果形式为女裙销量作为一列，儿童家具作为一列。

SQL：

SELECT
                SUM(CASE WHEN c.category_name = '女裙' THEN a.sales_volume ELSE 0 END) AS 女裙销量
                ,SUM(CASE WHEN c.category_name = '儿童家具' THEN a.sales_volume ELSE 0 END) AS 儿童家具销量
            FROM
                data_learning.product_order a
            LEFT JOIN
                (
                SELECT
                    *
                FROM
                    data_learning.product_category
                )c
                on a.category_id = c.category_id
              ;

结果如下：

1.2 使用if函数+聚合函数

同样是上面那个问题，用第二种方式如何实现呢？

SQL：

SELECT

                SUM(IF(c.category_name = '女裙',sales_volume,0 )) AS 女裙销量

                ,SUM(IF(c.category_name = '儿童家具',sales_volume,0)) AS 儿童家具销量

            FROM

                data_learning.product_order a

            LEFT JOIN

                SELECT

                FROM

                    data_learning.product_category

)c

                on a.category_id = c.category_id

结果如下：

在实际工作中，使用场景可能比这个复杂。

比如“请查询每个月女裙和儿童家具地销量，输出结果形式为女裙销量作为一列，儿童家具作为一列”。这个时候，可以结合GROUP BY语句去实现。

同时，聚合函数可能也不一定是SUM，有可能是MAX，这个都要根据具体的业务定义，数据表内容等去灵活运用。

列转行

2.1 使用 UNION ALL或UNION语句

我们将上面行转列的数据结果插入到一张临时表data_learning.tmp_test，然后用这个把这个表的结果做列转行操作，也就是女裙和儿童家具变成行。

step1 : 上面的查询结果存入临时表。（实际工作中不用，这里仅仅是想用上面的数据另建一张表作为操作示例）

CREATE TABLE data_learning.tmp_test AS

            SELECT

                SUM(IF(c.category_name = '女裙',sales_volume,0 )) AS 女裙销量

                ,SUM(IF(c.category_name = '儿童家具',sales_volume,0)) AS 儿童家具销量

            FROM

                data_learning.product_order a

            LEFT JOIN

                SELECT

                FROM

                    data_learning.product_category

)c

                on a.category_id = c.category_id

step2：做列转行操作

SQL：

SELECT

                '女裙' AS 类别

                ,女裙销量 AS 销量

            FROM

                data_learning.tmp_test UNION ALL

SELECT

                '儿童家具' AS 类别

                ,儿童家具销量 AS 销量

            FROM

                data_learning.tmp_test

结果如下：

行转列与列转行是SQL中非常重要的数据转换技巧。通过掌握这些技巧，你可以更加灵活地处理和分析数据，提高数据处理效率。希望本文能对你有所帮助！

该文章在 2024/2/1 23:19:14 编辑过

关键字查询

实现

sql

数据

正在查询...

点晴ERP是一款针对中小制造业的专业生产管理软件系统,系统成熟度和易用性得到了国内大量中小企业的青睐。

点晴PMS码头管理系统主要针对港口码头集装箱与散货日常运作、调度、堆场、车队、财务费用、相关报表等业务管理，结合码头的业务特点，围绕调度、堆场作业而开发的。集技术的先进性、管理的有效性于一体，是物流码头及其他港口类企业的高效ERP管理信息系统。

点晴WMS仓储管理系统提供了货物产品管理,销售管理,采购管理,仓储管理,仓库管理,保质期管理,货位管理,库位管理,生产管理,WMS管理系统,标签打印,条形码,二维码管理,批号管理软件。

点晴免费OA是一款软件和通用服务都免费，不限功能、不限时间、不限用户的免费OA协同办公管理系统。