工作中时长会用到竖表转横表(列转行)例如某商场每天都有营业额,数据库中营业额存储的方式是每天很多比每笔对应不同的消费记录
,可能有一天的营业额很多 对应的营业额明细就会很多,如果有个需求是要统计每天营业额多少,按天显示结果该怎么写?
下图的例子是我们数据库中一张记录每月营业情况的表,可以看到201501有很多笔记录,其他月份也有很多。
select
amt_fee
,
inst_date
from
R_KC_AGENT_STATISTICS
where
i nst_date
between
201501
and
201709;
实现方法很简单,此处需要使用oracle的decode函数。如果使用的是mysql则需要使用case when。当让oralce也可以使用case when来实现。
select sum(decode(inst_date, 201501, amt_fee, 0)) amtfees_201501,
sum(decode(inst_date, 201502, amt_fee, 0)) amtfees_201502,
sum(decode(inst_date, 201503, amt_fee, 0)) amtfees_201503,
sum(decode(inst_date, 201504, amt_fee, 0)) amtfees_201504,
sum(decode(inst_date, 201505, amt_fee, 0)) amtfees_201505,
sum(decode(inst_date, 201506, amt_fee, 0)) amtfees_201506,
sum(decode(inst_date, 201507, amt_fee, 0)) amtfees_201507,
sum(decode(inst_date, 201508, amt_fee, 0)) amtfees_201508,
sum(decode(inst_date, 201509, amt_fee, 0)) amtfees_201509,
sum(decode(inst_date, 2015010, amt_fee, 0)) amtfees_201510,
sum(decode(inst_date, 2015011, amt_fee, 0)) amtfees_201511,
sum(decode(inst_date, 2015012, amt_fee, 0)) amtfees_201512
from R_KC_AGENT_STATISTICS
where inst_date between 201501 and 201512;
查询结果如下:
如果有其他条件也可以在decode中再套一层decode。
再举一个复杂点的例子
转之前,同一个chnl_mcht_id,bank_mcht_id,app_key下 可能会有多个不同的acquire_type
select chnl_mcht_id,
BANK_MCHT_ID,
decode(max(wechats), 1, 'wechat', '') || '-' ||
decode(max(alipays), 1, 'alipay', '') || '-' ||
decode(max(qqs), 1, 'qq', '') || '-' ||
decode(max(jds), 1, 'jd', '') || '-' ||
decode(max(baidus), 1, 'baidus', ''),
APP_KEY
from (select chnl_mcht_id,
BANK_MCHT_ID,
decode(ACQUIRER_TYPE, 'wechat', 1, 0) wechats,
decode(ACQUIRER_TYPE, 'alipay', 1, 0) alipays,
decode(ACQUIRER_TYPE, 'qq', 1, 0) qqs,
decode(ACQUIRER_TYPE, 'jd', 1, 0) jds,
decode(ACQUIRER_TYPE, 'baidu', 1, 0) baidus,
APP_KEY
from (SELECT DISTINCT chnl_mcht_id,
BANK_MCHT_ID,
ACQUIRER_TYPE,
APP_KEY
FROM wallet_mcht_info_reg
WHERE chnl_mcht_id IS NOT NULL
group by chnl_mcht_id, BANK_MCHT_ID, APP_KEY, ACQUIRER_TYPE)
group by chnl_mcht_id, BANK_MCHT_ID, APP_KEY, ACQUIRER_TYPE)
group by chnl_mcht_id, BANK_MCHT_ID, APP_KEY
转之后同一个chnl_mcht_id,bank_mcht_id,app_key下面 不同的acquire_type 被压缩进一行