oracle 复杂语句

select nvl(sum1,'0')as sum1,nvl(sum2,'0') as sum2,da2 from(
select count(*) as sum1,substr(APPLY_DATE,6,2) as da1 from TBSHIPRUNCHECKAPPLY where apply_type like '%建造检验%'
and CHECK_TYPE  like '%新建%' and CHECK_ADDRESS like '%南京%' and  to_date(APPLY_DATE,'yyyy-MM-dd')
between to_date('2012-12-26','yyyy-mm-dd') and to_date('2013-12-25','yyyy-mm-dd') group by substr(APPLY_DATE,6,2))  a
left join  (
select count(*) as sum2,substr(APPLY_DATE,6,2) as da2 from TBSHIPRUNCHECKAPPLY where apply_type like '%建造检验%'
and CHECK_TYPE  like '%新建%' and (CHECK_ADDRESS not like '%南京%' or CHECK_ADDRESS is null) and  to_date(APPLY_DATE,'yyyy-MM-dd')
between to_date('2012-12-26','yyyy-mm-dd') and to_date('2013-12-25','yyyy-mm-dd')
group by substr(APPLY_DATE,6,2)) b on a.da1 = b.da2   order by da2

 

 

SELECT  '改建' as checktypename,COUNT (CASE WHEN substr(APPLY_DATE,6,2)='01' and CHECK_ADDRESS like '%南京%' then 1 end) as bdm1,
COUNT (CASE WHEN substr(APPLY_DATE,6,2)='01' and (CHECK_ADDRESS not like '%南京%' or CHECK_ADDRESS is null) then 1 end) as wdm1,
COUNT (CASE WHEN substr(APPLY_DATE,6,2)='02' and CHECK_ADDRESS like '%南京%' then 1 end) as bdm2,
COUNT (CASE WHEN substr(APPLY_DATE,6,2)='02' and (CHECK_ADDRESS not like '%南京%' or CHECK_ADDRESS is null) then 1 end) as wdm2,
COUNT (CASE WHEN substr(APPLY_DATE,6,2)='03' and CHECK_ADDRESS like '%南京%' then 1 end) as bdm3,
COUNT (CASE WHEN substr(APPLY_DATE,6,2)='03' and (CHECK_ADDRESS not like '%南京%' or CHECK_ADDRESS is null) then 1 end) as wdm3,
COUNT (CASE WHEN substr(APPLY_DATE,6,2)='04' and CHECK_ADDRESS like '%南京%' then 1 end) as bdm4,
COUNT (CASE WHEN substr(APPLY_DATE,6,2)='04' and (CHECK_ADDRESS not like '%南京%' or CHECK_ADDRESS is null) then 1 end) as wdm4,
COUNT (CASE WHEN substr(APPLY_DATE,6,2)='05' and CHECK_ADDRESS like '%南京%' then 1 end) as bdm5,
COUNT (CASE WHEN substr(APPLY_DATE,6,2)='05' and (CHECK_ADDRESS not like '%南京%' or CHECK_ADDRESS is null) then 1 end) as wdm5,
COUNT (CASE WHEN substr(APPLY_DATE,6,2)='06' and CHECK_ADDRESS like '%南京%' then 1 end) as bdm6,
COUNT (CASE WHEN substr(APPLY_DATE,6,2)='06' and (CHECK_ADDRESS not like '%南京%' or CHECK_ADDRESS is null) then 1 end) as wdm6,
COUNT (CASE WHEN substr(APPLY_DATE,6,2)='07' and CHECK_ADDRESS like '%南京%' then 1 end) as bdm7,
COUNT (CASE WHEN substr(APPLY_DATE,6,2)='07' and (CHECK_ADDRESS not like '%南京%' or CHECK_ADDRESS is null) then 1 end) as wdm7,
COUNT (CASE WHEN substr(APPLY_DATE,6,2)='08' and CHECK_ADDRESS like '%南京%' then 1 end) as bdm8,
COUNT (CASE WHEN substr(APPLY_DATE,6,2)='08' and (CHECK_ADDRESS not like '%南京%' or CHECK_ADDRESS is null) then 1 end) as wdm8,
COUNT (CASE WHEN substr(APPLY_DATE,6,2)='09' and CHECK_ADDRESS like '%南京%' then 1 end) as bdm9,
COUNT (CASE WHEN substr(APPLY_DATE,6,2)='09' and (CHECK_ADDRESS not like '%南京%' or CHECK_ADDRESS is null) then 1 end) as wdm9,
COUNT (CASE WHEN substr(APPLY_DATE,6,2)='10' and CHECK_ADDRESS like '%南京%' then 1 end) as bdm10,
COUNT (CASE WHEN substr(APPLY_DATE,6,2)='10' and (CHECK_ADDRESS not like '%南京%' or CHECK_ADDRESS is null) then 1 end) as wdm10,
COUNT (CASE WHEN substr(APPLY_DATE,6,2)='11' and CHECK_ADDRESS like '%南京%' then 1 end) as bdm11,
COUNT (CASE WHEN substr(APPLY_DATE,6,2)='11' and (CHECK_ADDRESS not like '%南京%' or CHECK_ADDRESS is null) then 1 end) as wdm11,
COUNT (CASE WHEN substr(APPLY_DATE,6,2)='12' and CHECK_ADDRESS like '%南京%' then 1 end) as bdm12,
COUNT (CASE WHEN substr(APPLY_DATE,6,2)='12' and (CHECK_ADDRESS not like '%南京%' or CHECK_ADDRESS is null) then 1 end) as wdm12
FROM TBSHIPRUNCHECKAPPLY where apply_type like '%建造检验%'
and CHECK_TYPE  like '%改建%'  and  to_date(APPLY_DATE,'yyyy-MM-dd')
between to_date('2012-12-26','yyyy-mm-dd') and to_date('2013-12-25','yyyy-mm-dd')

 

unoin 的时候 group by 不要每个都加  只能在union 之后  加一次就好了.