oracle case when 复杂统计SQL 博客分类： databasedatabase oracle

select substr(unitcode,0,6),
count(*),
--01--
sum(case substr(jqxzcode,0,4) when '0105' then 1 else 0 end) as sharen,
sum(case substr(jqxzcode,0,4) when '0109' then 1 else 0 end) as qiangjie,
sum(case substr(jqxzcode,0,4) when '0112' then 1 else 0 end) as qiangduo,
sum(case substr(jqxzcode,0,4) when '0110' then 1 else 0 end) as daoqie,
sum(case substr(jqxzcode,0,4) when '0114' then 1 else 0 end) as dubo,
sum(case when(jqxzcode like '01%' and jqxzcode not like '0105%' and jqxzcode not like '0109%' and jqxzcode not like '0112%' and jqxzcode not like '0110%' and jqxzcode not like '0114%') then 1 else 0 end) as qita1,
--02--
sum(case substr(jqxzcode,0,6) when '020108' then 1 else 0 end) as douou,
sum(case substr(jqxzcode,0,4) when '0207' then 1 else 0 end) as qiaozha,
sum(case when (jqxzcode like '02%' and jqxzcode not like '020108%' and jqxzcode not like '0207%') then 1 else 0 end) as qita2,
--qt--
sum(case substr(jqxzcode,0,2) when '08' then 1 else 0 end) as jiufen,
sum(case substr(jqxzcode,0,2) when '05' then 1 else 0 end) as qiuzhu,
sum(case substr(jqxzcode,0,4) when '0602' then 1 when '0603' then 1 else 0 end) as qiuzhu,
sum(case substr(jqxzcode,0,4) when '0601' then 1 else 0 end) as xiansuojubao,
sum(case substr(jqxzcode,0,2) when '03' then 1 else 0 end) as jiaotongshigu,
sum(case substr(jqxzcode,0,2) when '09' then 1 else 0 end) as shehuiliandong,
sum(case substr(jqxzcode,0,2) when '04' then 1 else 0 end) as huozhai,
sum(case when (jqxzcode like '%' and jqxzcode not like '01%' and jqxzcode not like '02%' and jqxzcode not like '03%' and jqxzcode not like '04%' and jqxzcode not like '05%' and jqxzcode not like '0601%' and jqxzcode not like '0602%' and jqxzcode not like '0603%' and jqxzcode not like '08%' and jqxzcode not like '09%' )then 1 else 0 end) as qita3
from jqfxk j
where
j.bjtime>to_date('2015-07-25 16:00:00','yyyy-mm-dd hh24:mi:ss')
 and
j.bjtime<to_date('2015-08-25 16:00:00','yyyy-mm-dd hh24:mi:ss')
 and
jqxzcode is not null and
unitcode in ('431100000000','431103000000','431102000000','431122000000','431121000000','431123000000','431129000000','431124000000','431128000000','431126000000','431129000000')
group by substr(unitcode,0,6)

 

以上SQL生成一个下面的表格统计数据：

横栏是unitcode单位

横栏是jqxzcode

 

 

 

select bzdwcode, jqtime,
       sum(cnt) as cnt,   ---日合计
       sum(num0) as num0, --其他类合计
       sum(num1) as num1, --抢  劫010501 总数
       sum(num2) as num2, --抢 持枪
       sum(num3) as num3, --抢 室外 摩托
        sum(num3) as num4,--抢 室外 汽车
         sum(num3) as num5,--抢 室外 财物
          sum(num3) as num6,--抢 入室 财物
           sum(num3) as num7,--抢夺 总数
            sum(num3) as num8,--抢夺  飞车
             sum(num3) as num9,--抢夺  非 飞车
              sum(num3) as num10, -- 盗 总数
              sum(num3) as num11, --盗 室外 摩托
              sum(num3) as num12,--盗 室外 汽车
              sum(num3) as num13,---盗 室外 财物
              sum(num3) as num14---盗 入室 财物
      from (
  select  substr(jqtime,1,10)  jqtime,
          case when substr(bzdwcode,1,6) not like '445221%' and
              substr(bzdwcode,1,6) not like '445222%' and 
              substr(bzdwcode,1,6) not like '445224%' and 
              substr(bzdwcode,1,6) not like '445281%' then '445200' else substr(bzdwcode,1,6) end  bzdwcode,
count(*) as cnt,
sum(case when (bzjqxzdm like '01%' or bzjqxzdm like '0414%')
     and bzjqxzdm not like '010501%' and bzjqxzdm not like '010504%' and bzjqxzdm not like '010502%' then 1 else 0 end) num0,
      
sum(case when bzjqxzdm like '010501%' then 1 else 0 end ) num1,
sum(case when bzjqxzdm like '01050150%' or bzjqxzdm like '01050160%'
or bzjqxzdm like '0105016A%' or bzjqxzdm like '0105016B%' then 1 else 0 end ) num2,
sum(case when bzjqxzdm like '0105012A%' then 1 else 0 end ) num3,
sum(case when bzjqxzdm like '0105012B%' or bzjqxzdm like '01050120%'  then 1 else 0 end ) num4,
sum(case when bzjqxzdm like '01050102%' then 1 else 0 end ) num5,
sum(case when bzjqxzdm like '01050101%' then 1 else 0 end ) num6,
 
sum(case when bzjqxzdm like '010504%' then 1 else 0 end) num7,
sum(case when bzjqxzdm like '01050401%' then 1 else 0 end) num8,
sum(case when bzjqxzdm like '010504%' and bzjqxzdm not like '01050401%' then 1 else 0 end) num9,
sum(case when bzjqxzdm like '010502%' then 1 else 0 end) num10,
sum(case when bzjqxzdm like '01050224%' then 1 else 0 end) num11,
sum(case when bzjqxzdm like '01050223%' then 1 else 0 end) num12,
sum(case when bzjqxzdm like '01050240%' then 1 else 0 end) num13,
sum(case when bzjqxzdm like '01050201%' then 1 else 0 end) num14
 from
 JQCASE t where  substr(bzdwcode,1,6) <> '445221' and  substr(bzdwcode,1,6) <> '445224' and  substr(bzdwcode,1,6) <> '445222' and  substr(bzdwcode,1,6) <> '445281' --and jqtime >=? and jqtime <=?
 group by substr(bzdwcode,1,6), substr(jqtime,1,10) )  a
 group by bzdwcode,jqtime order by jqtime, bzdwcode