在where语句中没有反应出ccatstep_tbl_montst_statisc a,CCATSTEP_TBL_REP_ENDOFFICE b两张表的关联呢?...
count(nvl(Ratedn_Max,0)) as line_count
from ccatstep_tbl_montst_statisc a left join CCATSTEP_TBL_REP_ENDOFFICE b
on
a.office_id = b.office_id
...将两张表外连接(注意该如何外连接),再使用count(nvl(Ratedn_Max,0))统计LINE_COUNT为0的数据。
count(nvl(Ratedn_Max,0)) as line_count
from ccatstep_tbl_montst_statisc a left join CCATSTEP_TBL_REP_ENDOFFICE b
on
a.office_id = b.office_id
...将两张表外连接(注意该如何外连接),再使用count(nvl(Ratedn_Max,0))统计LINE_COUNT为0的数据。
既需要得到新的结果:
MAXRATE LINE_COUNT
<1M 0 --先前忘记把这条写上
1M-2M 28
2M-3M 168
3M-4M 0
5M-6M 7
6M-7M 7
>7M 63
既需要得到新的结果:
MAXRATE LINE_COUNT
<1M 0
1M-2M 28
2M-3M 168
3M-4M 0
4M-5M 0 --先前又忘记把这条写上
5M-6M 7
6M-7M 7
>7M 63
tab_a as (
select '<1M' as maxrate from dual
union
select '1M-2M' from dual
union
select '2M-3M' from dual
union
select '3M-4M' from dual
union
select '4M-5M' from dual
union
select '5M-6M' from dual
union
select '6M-7M' from dual
),
tab_b as (
select case
when Ratedn_Max<1024 then '<1M'
when Ratedn_Max>=1024 and Ratedn_Max<2048 then '1M-2M'
when Ratedn_Max>=2048 and Ratedn_Max<3072 then '2M-3M'
when Ratedn_Max>=3072 and Ratedn_Max<4096 then '3M-4M'
when Ratedn_Max>=4096 and Ratedn_Max<5120 then '4M-5M'
when Ratedn_Max>=5120 and Ratedn_Max<6144 then '5M-6M'
when Ratedn_Max>=6144 and Ratedn_Max<7168 then '6M-7M'
when Ratedn_Max>=7168 then '>7M'
end maxrate, count(Ratedn_Max) as line_count
from ccatstep_tbl_montst_statisc a
where a.office_id = '001' and a.trunk_id = '003-49Z' and a.cabinet_id = '01003-043J'
group by case
when Ratedn_Max<1024 then '<1M'
when Ratedn_Max>=1024 and Ratedn_Max<2048 then '1M-2M'
when Ratedn_Max>=2048 and Ratedn_Max<3072 then '2M-3M'
when Ratedn_Max>=3072 and Ratedn_Max<4096 then '3M-4M'
when Ratedn_Max>=4096 and Ratedn_Max<5120 then '4M-5M'
when Ratedn_Max>=5120 and Ratedn_Max<6144 then '5M-6M'
when Ratedn_Max>=6144 and Ratedn_Max<7168 then '6M-7M'
when Ratedn_Max>=7168 then '>7M' end
)
select a.maxrate,sum(nvl(b.line_count,0)) line_count
from tab_a ,tab_b
where tab_a.maxrate=tab_b.maxrate(+)
group by a.maxrate
( select case when level = 1 then '<'||level||'M'
when level <= 7 then (level-1)||'M-'||level||'M'
else '>'||(level-1)||'M' end as MAXRATE, level as rid
from dual connect by level <= 8) a
left join
( select decode(sign(Ratedn_Max-7168),-1,(trunc(Ratedn_Max/1024)+1),8) as rid ,
count(Ratedn_Max) as line_count
from ccatstep_tbl_montst_statisc a,CCATSTEP_TBL_REP_ENDOFFICE b
where a.office_id = '001' and trunk_id = '003-49Z' and cabinet_id = '01003-043J'
group by decode(sign(Ratedn_Max-7168),-1,(trunc(Ratedn_Max/1024)+1),8)
) b
on a.rid = b.rid
select a.MAXRATE, nvl(b.line_count,0) from
(
select case when level = 1 then '<'||level||'M' when level <= 7 then (level-1)||'M-'||level||'M' else '>'||(level-1)||'M' end as MAXRATE, level as rid from dual connect by level <= 8
) a
left join
(
select decode(sign(Ratedn_Max-7168),-1,(trunc(Ratedn_Max/1024)+1),8) as rid , count(Ratedn_Max) as line_count from ccatstep_tbl_montst_statisc a,CCATSTEP_TBL_REP_ENDOFFICE b where a.office_id = '001' and trunk_id = '003-49Z' and cabinet_id = '01003-043J' group by decode(sign(Ratedn_Max-7168),-1,(trunc(Ratedn_Max/1024)+1),8)
) b
on a.rid = b.rid