SQL ：行专列示例

已于 2024-08-05 10:27:10 修改
阅读量1k 收藏 21
点赞数 28
文章标签： sql 数据库
于 2024-08-05 10:21:51 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_68627205/article/details/140919275
版权

1. 简介

在本教学文档中，我们将详细解释如何使用 SQL 查询将多行数据转为多列显示。我们通过聚合和计算消耗量来构建一个综合结果表。本文将详细解释每个步骤，并附上完整的 SQL 示例代码。

---------------------------------------------------------------------------------------------------------------------------------
附属：行转列的使用场景
 

行转列操作在数据分析和报表生成中常见，以下是几个主要的应用场景：

  1. 报表生成: 将数据按时间或类别展开，更直观地展示业务指标。

    • 示例: 销售报表中，将每个产品的月度销售额转置为列展示。

  2. 数据透视: 汇总数据时，将维度细节转为列数据，方便深入分析。

    • 示例: 广告支出按季度分渠道展示，观察总支出变化。

  3. 时间序列分析: 将不同时间点的数据展示在一行中，便于趋势分析。

    • 示例: 产品年度销售数据转置为按年份展示，查看销售趋势。

  4. 比较分析: 不同类别或时间段的数据对齐在同一行，便于直接对比。

    • 示例: 各地区销售数据转为列展示，比较不同地区的销售表现。

  5. 业务指标监控: 将关键业务指标按时间展开，便于管理层监控和分析。

    • 示例: 各部门月度绩效指标按部门展示，一目了然。
       

总结

行转列简化了数据展示，提升了数据分析的效率，在报表生成、数据透视、趋势分析和比较分析等场景中尤为实用。

---------------------------------------------------------------------------------------------------------------------------------

我们将模拟以下三个表：

  • china_gas_supply:

    • resource_name: VARCHAR, 资源名称
    • plan_supply_num: INT, 计划供应量
    • YEAR: VARCHAR, 年份
    • project_id: VARCHAR, 项目ID
    • analysis_id: VARCHAR, 分析ID
    • is_deleted: TINYINT, 是否删除标志

  • import_pipe_gas_supply:

    • resource_name: VARCHAR, 资源名称
    • plan_supply_num: INT, 计划供应量
    • YEAR: VARCHAR, 年份
    • project_id: VARCHAR, 项目ID
    • analysis_id: VARCHAR, 分析ID
    • is_deleted: TINYINT, 是否删除标志

  • lng_gas_supply:

    • resource_name: VARCHAR, 资源名称
    • plan_supply_num: INT, 计划供应量
    • YEAR: VARCHAR, 年份
    • project_id: VARCHAR, 项目ID
    • analysis_id: VARCHAR, 分析ID
    • is_deleted: TINYINT, 是否删除标志

3. SQL 查询

下面的 SQL 查询将实现以下目标：

  1. 汇总三个表中的天然气供应计划数据。
  2. 按年计算每种资源的总供应量。
  3. 计算所有资源的年度总供应量和消耗量。
  4. 将结果转置为列格式，方便展示。
     
WITH aggregated AS (
    SELECT
        resource_name,
        COALESCE(SUM(CASE WHEN YEAR = '2023' THEN plan_supply_num ELSE 0 END), 0) AS "2023",
        COALESCE(SUM(CASE WHEN YEAR = '2024' THEN plan_supply_num ELSE 0 END), 0) AS "2024",
        COALESCE(SUM(CASE WHEN YEAR = '2025' THEN plan_supply_num ELSE 0 END), 0) AS "2025",
        COALESCE(SUM(CASE WHEN YEAR = '2026' THEN plan_supply_num ELSE 0 END), 0) AS "2026",
        COALESCE(SUM(CASE WHEN YEAR = '2027' THEN plan_supply_num ELSE 0 END), 0) AS "2027",
        COALESCE(SUM(CASE WHEN YEAR = '2028' THEN plan_supply_num ELSE 0 END), 0) AS "2028",
        COALESCE(SUM(CASE WHEN YEAR = '2029' THEN plan_supply_num ELSE 0 END), 0) AS "2029" 
    FROM
    (
        SELECT
            resource_name,
            plan_supply_num,
            YEAR 
        FROM
            china_gas_supply
        WHERE
            project_id = '19c32eaa73ca4732bccfdc4c9ff74373' 
            AND analysis_id = 'ce7f2fbb-7db6-4380-899f-e7ec18ac92f6' 
            AND is_deleted = 0
        UNION ALL
        SELECT
            resource_name,
            plan_supply_num,
            YEAR 
        FROM
            import_pipe_gas_supply
        WHERE
            project_id = '19c32eaa73ca4732bccfdc4c9ff74373' 
            AND analysis_id = 'ce7f2fbb-7db6-4380-899f-e7ec18ac92f6' 
            AND is_deleted = 0
        UNION ALL
        SELECT
            resource_name,
            plan_supply_num,
            YEAR 
        FROM
            lng_gas_supply
        WHERE
            project_id = '19c32eaa73ca4732bccfdc4c9ff74373' 
            AND analysis_id = 'ce7f2fbb-7db6-4380-899f-e7ec18ac92f6' 
            AND is_deleted = 0
    ) AS combined 
    GROUP BY
        resource_name 
),
totals AS (
    SELECT
        COALESCE(SUM("2023"), 0) AS "2023",
        COALESCE(SUM("2024"), 0) AS "2024",
        COALESCE(SUM("2025"), 0) AS "2025",
        COALESCE(SUM("2026"), 0) AS "2026",
        COALESCE(SUM("2027"), 0) AS "2027",
        COALESCE(SUM("2028"), 0) AS "2028",
        COALESCE(SUM("2029"), 0) AS "2029" 
    FROM
        aggregated 
),
consumption AS (
    SELECT
        resource_name,
        COALESCE((SELECT "2023" FROM totals) - "2023", 0) AS "2023",
        COALESCE((SELECT "2024" FROM totals) - "2024", 0) AS "2024",
        COALESCE((SELECT "2025" FROM totals) - "2025", 0) AS "2025",
        COALESCE((SELECT "2026" FROM totals) - "2026", 0) AS "2026",
        COALESCE((SELECT "2027" FROM totals) - "2027", 0) AS "2027",
        COALESCE((SELECT "2028" FROM totals) - "2028", 0) AS "2028",
        COALESCE((SELECT "2029" FROM totals) - "2029", 0) AS "2029" 
    FROM
        aggregated 
),
combined_results AS (
    SELECT
        resource_name,
        "2023",
        "2024",
        "2025",
        "2026",
        "2027",
        "2028",
        "2029",
        1 AS order_priority 
    FROM
        aggregated
    UNION ALL
    SELECT
        '合计' AS resource_name,
        COALESCE(SUM("2023"), 0),
        COALESCE(SUM("2024"), 0),
        COALESCE(SUM("2025"), 0),
        COALESCE(SUM("2026"), 0),
        COALESCE(SUM("2027"), 0),
        COALESCE(SUM("2028"), 0),
        COALESCE(SUM("2029"), 0),
        2 AS order_priority 
    FROM
        aggregated
    UNION ALL
    SELECT
        '消费量' AS resource_name,
        COALESCE(SUM("2023"), 0),
        COALESCE(SUM("2024"), 0),
        COALESCE(SUM("2025"), 0),
        COALESCE(SUM("2026"), 0),
        COALESCE(SUM("2027"), 0),
        COALESCE(SUM("2028"), 0),
        COALESCE(SUM("2029"), 0),
        3 AS order_priority 
    FROM
        consumption 
) 
SELECT
    resource_name,
    "2023",
    "2024",
    "2025",
    "2026",
    "2027",
    "2028",
    "2029" 
FROM
    combined_results 
ORDER BY
    order_priority;

4. SQL 代码解析

4.1. 聚合数据 (aggregated CTE)

aggregated CTE 首先从三个供应表中提取数据，通过 UNION ALL 将它们合并为一个临时表。接着，按 resource_name 对每年数据进行分组聚合，计算每个资源的年计划供应量。
 

WITH aggregated AS (
    SELECT
        resource_name,
        COALESCE(SUM(CASE WHEN YEAR = '2023' THEN plan_supply_num ELSE 0 END), 0) AS "2023",
        COALESCE(SUM(CASE WHEN YEAR = '2024' THEN plan_supply_num ELSE 0 END), 0) AS "2024",
        -- 其他年份同理
    FROM
    (
        SELECT
            resource_name,
            plan_supply_num,
            YEAR 
        FROM
            china_gas_supply
        WHERE
            project_id = '19c32eaa73ca4732bccfdc4c9ff74373' 
            AND analysis_id = 'ce7f2fbb-7db6-4380-899f-e7ec18ac92f6' 
            AND is_deleted = 0
        UNION ALL
        SELECT
            resource_name,
            plan_supply_num,
            YEAR 
        FROM
            import_pipe_gas_supply
        WHERE
            project_id = '19c32eaa73ca4732bccfdc4c9ff74373' 
            AND analysis_id = 'ce7f2fbb-7db6-4380-899f-e7ec18ac92f6' 
            AND is_deleted = 0
        UNION ALL
       ```sql
        SELECT
            resource_name,
            plan_supply_num,
            YEAR 
        FROM
            lng_gas_supply
        WHERE
            project_id = '19c32eaa73ca4732bccfdc4c9ff74373' 
            AND analysis_id = 'ce7f2fbb-7db6-4380-899f-e7ec18ac92f6' 
            AND is_deleted = 0
    ) AS combined 
    GROUP BY
        resource_name 
)

上述 SQL 代码片段实现了以下操作：

  • 从 china_gas_supply、import_pipe_gas_supply 和 lng_gas_supply 三个表中获取数据。
  • 通过 UNION ALL 合并这些表的记录。
  • 使用 COALESCE 和 CASE 语句对每一年的数据进行汇总。
  • 最终根据 resource_name 对数据进行分组，以便在后续步骤中转置数据。

4.2. 计算年度总计 (totals CTE)

接下来，通过 totals CTE 计算所有资源的年度总计。
 

totals AS (
    SELECT
        COALESCE(SUM("2023"), 0) AS "2023",
        COALESCE(SUM("2024"), 0) AS "2024",
        COALESCE(SUM("2025"), 0) AS "2025",
        COALESCE(SUM("2026"), 0) AS "2026",
        COALESCE(SUM("2027"), 0) AS "2027",
        COALESCE(SUM("2028"), 0) AS "2028",
        COALESCE(SUM("2029"), 0) AS "2029" 
    FROM
        aggregated 
)

这里，我们从 aggregated CTE 中提取每年的数据，并计算所有资源的年总供应量。

 

4.3. 计算消耗量 (consumption CTE)

在 consumption CTE 中，我们计算每种资源在每年的消耗量，即年度总量减去该资源的年度供应量。

consumption AS (
    SELECT
        resource_name,
        COALESCE((SELECT "2023" FROM totals) - "2023", 0) AS "2023",
        COALESCE((SELECT "2024" FROM totals) - "2024", 0) AS "2024",
        COALESCE((SELECT "2025" FROM totals) - "2025", 0) AS "2025",
        COALESCE((SELECT "2026" FROM totals) - "2026", 0) AS "2026",
        COALESCE((SELECT "2027" FROM totals) - "2027", 0) AS "2027",
        COALESCE((SELECT "2028" FROM totals) - "2028", 0) AS "2028",
        COALESCE((SELECT "2029" FROM totals) - "2029", 0) AS "2029" 
    FROM
        aggregated 
)

这个 CTE 从 aggregated 中提取资源的年度供应数据，并使用 totals 中的年度总计数据来计算消耗量。

 

4.4. 合并结果并排序 (combined_results CTE)

在 combined_results CTE 中，我们将前面的结果合并，并按优先级排序，以生成最终结果。
 

combined_results AS (
    SELECT
        resource_name,
        "2023",
        "2024",
        "2025",
        "2026",
        "2027",
        "2028",
        "2029",
        1 AS order_priority 
    FROM
        aggregated
    UNION ALL
    SELECT
        '合计' AS resource_name,
        COALESCE(SUM("2023"), 0),
        COALESCE(SUM("2024"), 0),
        COALESCE(SUM("2025"), 0),
        COALESCE(SUM("2026"), 0),
        COALESCE(SUM("2027"), 0),
        COALESCE(SUM("2028"), 0),
        COALESCE(SUM("2029"), 0),
        2 AS order_priority 
    FROM
        aggregated
    UNION ALL
    SELECT
        '消费量' AS resource_name,
        COALESCE(SUM("2023"), 0),
        COALESCE(SUM("2024"), 0),
        COALESCE(SUM("2025"), 0),
        COALESCE(SUM("2026"), 0),
        COALESCE(SUM("2027"), 0),
        COALESCE(SUM("2028"), 0),
        COALESCE(SUM("2029"), 0),
        3 AS order_priority 
    FROM
        consumption 
)

这里，我们将每个资源的供应数据、合计数据和消耗数据汇总到一起，并根据 order_priority 设置的优先级进行排序。

 

4.5. 最终查询结果

最终，我们从 combined_results 中提取数据并按优先级排序：
 

SELECT
    resource_name,
    "2023",
    "2024",
    "2025",
    "2026",
    "2027",
    "2028",
    "2029" 
FROM
    combined_results 
ORDER BY
    order_priority;

5. 示例数据和输出

  • china_gas_supply:

    resource_nameplan_supply_numYEARproject_idanalysis_idis_deleted
    资源A1002023proj_123analy_4560
    资源B2002023proj_123analy_4560

  • import_pipe_gas_supply:

    resource_nameplan_supply_numYEARproject_idanalysis_idis_deleted
    资源A1502024proj_123analy_4560
    资源C3002024proj_123analy_4560

  • lng_gas_supply:

    resource_nameplan_supply_numYEARproject_idanalysis_idis_deleted
    资源B2502025proj_123analy_4560

运行上述 SQL 查询后，输出结果将类似于以下表格

resource_name2023202420252026202720282029
资源A10015000000
资源B20002500000
资源C030000000
合计3004502500000
消费量0000000

6. 总结

通过使用模拟表结构，我们展示了如何使用 SQL 查询来聚合数据、计算合计与消耗量，并将结果转置为多列展示。这种行转列操作在数据分析和报表生成中非常有用，并且 CTE（公共表表达式）在简化复杂查询中的作用尤为突出。

代码狂想曲
关注
  • 28
    点赞
  • 踩
  • 21
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
sql将多行数据合并为一行多列_sql复杂查询
weixin_39583623的博客
12-09 4869
一、视图（一）什么是视图有些sql语句你经常用，就可以保存到视图中，以后就不用再重新输入了，这样大大的提高了效率。注意！视图存放的不是数据，而且sql语句，所以数据库更新时，它也更新了，而且它不暂空间。（二）如何创建视图写完sql后，点运行，再右击视图，点刷新即可，下图就是创建好了视图。注意！select后的查询语句和视图名称（上图是“性别汇总”）后的列是一一对应的。（三）如何使用视图因为之前已经...
sql 行转列
qq_42807253的博客
11-02 3748
case when 与 pivot
SQL多列合并成一列
折翼的青鸟
06-29 1万+
在SQL中，拼接多个数据列成为一列是很常见的需求，在SQL中有很多函数可以实现这个需求，例如concat、concat_ws、case和coalesce等函数。在使用这些函数时，需要注意空值的处理，以及要拼接的字符串的格式和分隔符。
sql列转行 行转列
最新发布
weixin_48485132的博客
07-23 3406
在 SQL 中，转换数据以按列排列的值成为按行排列的值（即所谓的“列转行”或“列转行”）是常见的数据操作需求。这个操作在不同的数据库管理系统中可以通过不同的技术手段来实现。在 SQL 中，行转列（也称为“透视”或“旋转”）的操作是将行数据转换为列数据，这在数据总结和分析中非常有用。不同的数据库系统有不同的方法来实现行转列的操作。操作的数据库，如 Oracle 和 SQL Server，可以直接使用。将多个列的数据合并到一个新的结果集的单一列中。如果你的列数是固定的，可以使用。函数可以实现类似的操作。
SQL行转列
weixin_66940857的博客
03-19 835
SQL行转列
SQL中行转列的方法【简单详细】
aristo
08-15 1655
Sql中的的PIVOT:最近自己在,发现要拿取查询某一列的数据转成行输出为一张临时表引用但是:突然发现,怎么用SQL实现列转行;发现PIVOT真好适合(下饭~学习一波!)(参考好几篇…)
建议收藏丨sql行转列的一千种写法！！
2401_83817024的博客
04-10 994
-【
SQL查询多行转一行多列
子非鱼39938的博客
04-25 4242
SQL查询多行转一行多列
sql 行专列 列转行 普通行列转换
05-27
在SQL数据库操作中，行与列的转换是常见的数据处理需求。这通常涉及到将一列的数据拆分成多行，或者将多行数据合并为一列。这种操作在数据分析、报表生成以及数据清洗过程中非常常见。在SQL Server中，我们可以利用...
T-SQL_经典行专列、列转行_分页及存储过程
08-17
### T-SQL经典编程之行转列与列转行技巧详解 #### 一、T-SQL 行转列 在数据库处理中，行转列是一种常见的数据转换需求，特别是当需要将多行记录中的某一列数据按照特定条件聚合到一行中的不同列时。 **实例背景**...
SQL Server里PIVOT运算符的”红颜祸水“
12-14
以下是一个手动转换的示例： ```sql SELECT RecordID, MAX(CASE WHEN Element = 'FirstName' THEN Value END) AS 'FirstName', MAX(CASE WHEN Element = 'LastName' THEN Value END) AS 'LastName', MAX(CASE ...
SQL查询语句行转列横向显示实例解析
09-10
主要为大家详细介绍了两个关于SQL查询语句行转列横向显示的实例解析，感兴趣的小伙伴们可以参考一下
sql 行转列 静态、动态 实例
11-24
以下是一个示例： 首先，我们创建一个名为`chun_test`的测试表，并插入数据： ```sql CREATE TABLE chun_test ( id INT, name VARCHAR(50), dr VARCHAR(50) ); INSERT INTO chun_test (id, name, dr) VALUES (1...
java行转列的例子
12-10
在提供的"行转列"压缩包文件中，可能包含了实现这个功能的示例代码或工具类。通过阅读和学习这些代码，你可以进一步理解如何在实际项目中应用这些概念和技术。记得在实际操作时，根据具体需求调整和优化代码，以实现...
sql行转列三个方法
miaoao611的博客
08-15 6975
select uid, sum(if(course=‘语文’,score,NULL)) as。sum((if(course=‘数学’,score,NULL)) as。由多行变一行，group by聚合，由一列变多列，衍生提前。select uid, ‘数学’ as course,select uid, ‘语文’ as course，1.行转列sum+if/case when。2.列转行union。...
主流数据库（SQL Server、Mysql、Oracle）通过sql实现多行数据合为一行
m0_59415345的博客
03-23 8495
FOR XML是 SQL Server 提供的一种功能，允许您将查询结果转换为 XML 格式。PATH模式则是其中一种灵活的方式来构造自定义的XML结构。： 当您想从单列中提取所有行的数据并连接成一个字符串列表时，可以使用。例如，假设有一个包含开发任务名称的rwmc这段sql执行的结果将会返回一个XML字符串，其中每个任务名称后面跟着逗号，所有名称连接在一起。这将返回每个部门名称都在元素内的XML结构。： 若要创建更复杂的嵌套结构，可以结合AS。
SQL之行列转置以及非常规的行列转置
zxcvbnm0207的博客
06-14 1728
创建数据表# 插入数据values ('张三', '语文', 80),('张三', '数学', 90),('张三', '物理', 85),('李四', '语文', 85),('李四', '数学', 92),('李四', '物理', 82);
sql查询：行转列和 列转行
热门推荐
weixin_40482816的博客
10-31 1万+
一、行转列 将原本同一列下多行的不同内容作为多个字段，输出对应内容。 表及数据sql： CREATE TABLE `tb_score` ( `id` int(11) NOT NULL AUTO_INCREMENT, `userid` varchar(20) NOT NULL COMMENT '用户id', `subjectName` varchar(20) DEFAULT NULL COMMENT '科目', `score` double DEFAULT NULL COM...
SQL多行转一行（一列转多列）
李玉坤的博客
07-26 6712
前景 apply_id bigint COMMENT ‘申请id’, oss_key string COMMENT ‘存储在oss上的路径’, type string COMMENT ‘类型,10-身份证数据,20-人脸照,30-运营商,40-魔杖报告,50-补充信息’, SELECT apply_id ,type ,oss_key FROM zizhirisk.ods_t_apply...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值