行转列之decode、case when

来源：互联网发布：网络储存硬盘编辑：程序博客网时间：2024/04/29 05:57

行转列统计方法，一般出现在项目中的多维度统计报表中。decode编写简洁，case when应用较灵活；decode目前只在Oracle和DB2数据库中测试可用，其他数据库的兼容性没有测试过；case when 适合大部分数据库，兼容性比较好。

简单案例：

1、创建测试表

CREATE TABLE TEST_BUSINESS(COMPANYNAME VARCHAR(20),CHANNELCODE VARCHAR(20),MONEY DECIMAL(20,2),AC_TYPE VARCHAR(20),CREATE_TIME VARCHAR(10));

2、插入部分测试数据

INSERT INTO TEST_BUSINESS VALUES ('北京','通联','8000','首期','2016-01-01');INSERT INTO TEST_BUSINESS VALUES ('北京','通联','7000','续期','2016-01-01');INSERT INTO TEST_BUSINESS VALUES ('上海','通联','8000','首期','2016-01-01');INSERT INTO TEST_BUSINESS VALUES ('上海','通联','7000','续期','2016-01-01');INSERT INTO TEST_BUSINESS VALUES ('北京','上海银联','4000','首期','2016-01-01');INSERT INTO TEST_BUSINESS VALUES ('北京','上海银联','3000','续期','2016-01-01');INSERT INTO TEST_BUSINESS VALUES ('上海','上海银联','4000','首期','2016-01-01');INSERT INTO TEST_BUSINESS VALUES ('上海','上海银联','3000','续期','2016-01-01');INSERT INTO TEST_BUSINESS VALUES ('北京','广州银联','10000','首期','2016-01-01');INSERT INTO TEST_BUSINESS VALUES ('北京','广州银联','9000','续期','2016-01-01');INSERT INTO TEST_BUSINESS VALUES ('上海','广州银联','6000','首期','2016-01-01');INSERT INTO TEST_BUSINESS VALUES ('上海','广州银联','5000','续期','2016-01-01');

3、decode实现

SELECT t.COMPANYNAME AS 公司,t.CREATE_TIME AS 交易时间,sum(decode(t.CHANNELCODE||t.AC_TYPE,'通联首期',t.MONEY,0)) AS 通联首期,sum(decode(t.CHANNELCODE||t.AC_TYPE,'通联续期',t.MONEY,0)) AS 通联续期,sum(decode(t.CHANNELCODE||t.AC_TYPE,'广州银联首期',t.MONEY,0)) AS 广州银联首期,sum(decode(t.CHANNELCODE||t.AC_TYPE,'广州银联续期',t.MONEY,0)) AS 广州银联续期,sum(decode(t.CHANNELCODE||t.AC_TYPE,'上海银联首期',t.MONEY,0)) AS 上海银联首期,sum(decode(t.CHANNELCODE||t.AC_TYPE,'上海银联续期',t.MONEY,0)) AS 上海银联续期FROM TEST_BUSINESS tGROUP BY t.COMPANYNAME ,t.CREATE_TIME ;

4、case when实现

SELECT t.COMPANYNAME AS 公司,t.CREATE_TIME AS 交易时间,sum(CASE WHEN t.CHANNELCODE||t.AC_TYPE = '通联首期' THEN t.MONEY ELSE 0 END) AS 通联首期,sum(CASE WHEN t.CHANNELCODE||t.AC_TYPE = '通联续期' THEN t.MONEY ELSE 0 END)  AS 通联续期,sum(CASE WHEN t.CHANNELCODE||t.AC_TYPE = '广州银联首期' THEN t.MONEY ELSE 0 END)  AS 广州银联首期,sum(CASE WHEN t.CHANNELCODE||t.AC_TYPE = '广州银联续期' THEN t.MONEY ELSE 0 END)  AS 广州银联续期,sum(CASE WHEN t.CHANNELCODE||t.AC_TYPE = '上海银联首期' THEN t.MONEY ELSE 0 END)  AS 上海银联首期,sum(CASE WHEN t.CHANNELCODE||t.AC_TYPE = '上海银联续期' THEN t.MONEY ELSE 0 END)  AS 上海银联续期FROM TEST_BUSINESS t GROUP BY t.COMPANYNAME,t.CREATE_TIME ;

5、输出结果一样

0 0