利用sum来实现行列转换

最新推荐文章于 2024-02-01 17:18:00 发布

weixin_30585437

最新推荐文章于 2024-02-01 17:18:00 发布

阅读量104

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/Automation_software/archive/2013/01/21/2869340.html

本文介绍了一种使用SQL查询语句来实现数据聚合的方法，通过CASE WHEN语句和GROUP BY子句，可以有效地按不同条件汇总数据。具体展示了如何将表中的特定IDX_CODE对应的DATA_VALUE进行汇总，并展示在同一结果集中。

原始表如下：

预期想要将表中的每一个IDX_CODE分别在查询结果中各显示一列，结果表如下：

建表语句如下：

create table test
(
  IDX_DATA_ID   NUMBER(12) not null,
  org_no        NUMBER(2),
  org_name      VARCHAR2(6),
  idx_code      VARCHAR2(12),
  stat_cycle    NUMBER(6),
  data_value    NUMBER(12),
  CHAIN_VALUE   NUMBER(6,2)
)

插入数据：

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)
values (1300025889, 1, '北京', 'ZH001360', 201210, 100000, 0.00);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)
values (1300025890, 1, '北京', 'ZH001360', 201211, 110000, 1.10);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)
values (1300025891, 1, '北京', 'ZH001360', 201212, 90000, 0.82);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)
values (1300025892, 1, '北京', 'ZH001359', 201210, 200000, 0.00);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)
values (1300025893, 1, '北京', 'ZH001359', 201211, 210000, 1.05);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)
values (1300025894, 1, '北京', 'ZH001359', 201212, 190000, 0.90);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)
values (1300025895, 2, '上海', 'ZH001360', 201210, 100000, 0.00);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)
values (1300025896, 2, '上海', 'ZH001360', 201211, 110000, 1.10);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)
values (1300025897, 2, '上海', 'ZH001360', 201212, 90000, 0.82);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)
values (1300025898, 2, '上海', 'ZH001359', 201210, 200000, 0.00);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)
values (1300025899, 2, '上海', 'ZH001359', 201211, 210000, 1.05);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)
values (1300025900, 2, '上海', 'ZH001359', 201212, 190000, 0.90);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)
values (1300025901, 3, '天津', 'ZH001360', 201210, 100000, 0.00);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)
values (1300025902, 3, '天津', 'ZH001360', 201211, 110000, 1.10);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)
values (1300025903, 3, '天津', 'ZH001360', 201212, 90000, 0.82);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)
values (1300025904, 3, '天津', 'ZH001359', 201210, 200000, 0.00);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)
values (1300025905, 3, '天津', 'ZH001359', 201211, 210000, 1.05);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)
values (1300025906, 3, '天津', 'ZH001359', 201212, 190000, 0.90);

实现预期结果的查询语句如下：

SELECT T1.ORG_NAME,
       T1.STAT_CYCLE,
       SUM(CASE
             WHEN T1.IDX_CODE = 'ZH001359' THEN
              T1.DATA_VALUE
           END) AS DV1,
       SUM(CASE
             WHEN T1.IDX_CODE = 'ZH001360' THEN
              T1.DATA_VALUE
           END) AS DV2,
       CAST(SUM(CASE
                  WHEN T1.IDX_CODE = 'ZH001359' THEN
                   T1.CHAIN_VALUE
                END) AS NUMBER(6, 2)) AS DV3
  FROM TEST T1
 WHERE T1.IDX_CODE IN ('ZH001359', 'ZH001360')
   AND T1.STAT_CYCLE >= '201201'
   AND T1.STAT_CYCLE <= '201212'
   AND T1.ORG_NO = '1'
 GROUP BY T1.STAT_CYCLE, T1.ORG_NAME
 ORDER BY STAT_CYCLE DESC;

附：另外一种写法：

SELECT T1.ORG_NAME,
       T1.STAT_CYCLE,
       T1.DATA_VALUE AS DV1,
       (SELECT T2.DATA_VALUE
          FROM TEST T2
         WHERE T2.IDX_CODE = 'ZH001360'
           AND T1.STAT_CYCLE = T2.STAT_CYCLE AND t2.ORG_NO = '1') AS DV2,
       T1.CHAIN_VALUE AS DV3
  FROM TEST T1
 WHERE T1.IDX_CODE = 'ZH001359'
   AND T1.STAT_CYCLE >= '201201'
   AND T1.STAT_CYCLE <= '201212'
   AND T1.ORG_NO = '1';