ORACLE 交叉报表固定列实现方法
需求:
统计某些天内每个类型的数量,
原数据 MCTYPE 类型:DTH,MED,OTH
TDay时间划分:T1,T2,T3,T4~T10
原始data
如果DTHIND=1 那么这条记录类型就是DTH类型,其他类推
id | DTHIND | MEDIND | OTHIND |
---|---|---|---|
16006723 | 0 | 1 | 0 |
16006730 | 0 | 1 | 0 |
16006735 | 0 | 1 | 0 |
16006744 | 1 | 0 | 0 |
16006757 | 1 | 0 | 0 |
16006772 | 1 | 0 | 0 |
16007009 | 0 | 1 | 0 |
使用交叉报表后show 出的data
MCTYPE | T1 | T2 | T3 | T4~T10 |
---|---|---|---|---|
DTH | 1 | 0 | 0 | 2 |
MED | 0 | 0 | 1 | 3 |
但是Clients 需求是不管是否有data,都要show 出全部类型,全部日期
期望结果是
MCTYPE | T1 | T2 | T3 | T4~T10 |
---|---|---|---|---|
DTH | 1 | 0 | 0 | 2 |
MED | 0 | 0 | 1 | 3 |
OTH | 0 | 0 | 0 | 0 |
原代码贴上代码
select MCTYPE,
COUNT(DECODE(TDATE,'T1','T1',NULL)) AS T1,
COUNT(DECODE(TDATE,'T2','T2',NULL)) AS T2,
COUNT(DECODE(TDATE,'T3','T3',NULL)) AS T3,
COUNT(DECODE(TDATE,'T4~T10','T4',NULL)) AS "T4~T10" FROM (
select case when sc.dthind=1 then 'DTH'
when sc.medind=1 then 'MED'
when sc.othind=1 then 'OTH' else null end as mctype,
case when sc.crtdate >=(SELECT BIZDATE FROM SYSINFO) and sc.crtdate<=(SELECT BIZDATE+1 FROM SYSINFO) then 'T0'
when sc.crtdate >=(SELECT BIZDATE-1 FROM SYSINFO) and sc.crtdate<=(SELECT BIZDATE FROM SYSINFO) then 'T1'
when sc.crtdate >=(SELECT BIZDATE-2 FROM SYSINFO) and sc.crtdate<=(SELECT BIZDATE-1 FROM SYSINFO) then 'T2'
when sc.crtdate >=(SELECT BIZDATE-3 FROM SYSINFO) and sc.crtdate<=(SELECT BIZDATE-2 FROM SYSINFO) then 'T3'
when sc.crtdate >=(SELECT BIZDATE-10 FROM SYSINFO) and sc.crtdate<=(SELECT BIZDATE-3 FROM SYSINFO) then 'T4~T10'
else null end as Tdate
from cccbsc sc where sc.clmnum in ('16006772','16007009','16006744','16006757','16006723','16006730','16006735')
and crtdate >=(SELECT BIZDATE - 10 FROM SYSINFO)
AND crtdate < (SELECT BIZDATE FROM SYSINFO)) GROUP BY MCTYPE
实现方法是
union all 所有的行和列一遍,这里是3*4,也就是union all 12条记录,然后所有统计的数量
减一条
select MCTYPE,
COUNT(DECODE(TDATE,'T1','T1',NULL))-1 AS T1,
COUNT(DECODE(TDATE,'T2','T2',NULL))-1 AS T2,
COUNT(DECODE(TDATE,'T3','T3',NULL))-1 AS T3,
COUNT(DECODE(TDATE,'T4~T10','T4',NULL))-1 AS "T4~T10" FROM (
select case when sc.dthind=1 then 'DTH'
when sc.medind=1 then 'MED'
when sc.othind=1 then 'OTH' else null end as mctype,
case when sc.crtdate >=(SELECT BIZDATE FROM SYSINFO) and sc.crtdate<=(SELECT BIZDATE+1 FROM SYSINFO) then 'T0'
when sc.crtdate >=(SELECT BIZDATE-1 FROM SYSINFO) and sc.crtdate<=(SELECT BIZDATE FROM SYSINFO) then 'T1'
when sc.crtdate >=(SELECT BIZDATE-2 FROM SYSINFO) and sc.crtdate<=(SELECT BIZDATE-1 FROM SYSINFO) then 'T2'
when sc.crtdate >=(SELECT BIZDATE-3 FROM SYSINFO) and sc.crtdate<=(SELECT BIZDATE-2 FROM SYSINFO) then 'T3'
when sc.crtdate >=(SELECT BIZDATE-10 FROM SYSINFO) and sc.crtdate<=(SELECT BIZDATE-3 FROM SYSINFO) then 'T4~T10'
else null end as Tdate
from cccbsc sc where sc.clmnum in ('16006772','16007009','16006744','16006757','16006723','16006730','16006735')
and crtdate >=(SELECT BIZDATE - 10 FROM SYSINFO)
AND crtdate < (SELECT BIZDATE FROM SYSINFO)
union all select 'DTH' AS MCTYPE,'T1' AS TDATE FROM DUAL
union all select 'DTH' AS MCTYPE,'T2' AS TDATE FROM DUAL
union all select 'DTH' AS MCTYPE,'T3' AS TDATE FROM DUAL
union all select 'DTH' AS MCTYPE,'T4~T10' AS TDATE FROM DUAL
union all select 'MED' AS MCTYPE,'T1' AS TDATE FROM DUAL
union all select 'MED' AS MCTYPE,'T2' AS TDATE FROM DUAL
union all select 'MED' AS MCTYPE,'T3' AS TDATE FROM DUAL
union all select 'MED' AS MCTYPE,'T4~T10' AS TDATE FROM DUAL
union all select 'OTH' AS MCTYPE,'T1' AS TDATE FROM DUAL
union all select 'OTH' AS MCTYPE,'T2' AS TDATE FROM DUAL
union all select 'OTH' AS MCTYPE,'T3' AS TDATE FROM DUAL
union all select 'OTH' AS MCTYPE,'T4~T10' AS TDATE FROM DUAL) GROUP BY MCTYPE
;
原理其实就是group by 的时候让每个行和列都有值,然后show的时候再把加多的减去就可以了。
如果有什么其他简单的方法可以固定行和列也可以告诉我,谢谢