使用Case语句如何得到下面的结果

[cyberwjw]

在Oracle中执行下面的语句：
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,CCATSTEP_TBL_REP_ENDOFFICE b
where a.office_id = '001' and trunk_id = '003-49Z' and 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
得到结果：
MAXRATE LINE_COUNT
1M-2M 28
2M-3M 168
5M-6M 7
6M-7M 7
>7M 63
我想得到如果LINE_COUNT是0的也要显示出来，这个如何实现？
既需要得到新的结果：
MAXRATE LINE_COUNT
1M-2M 28
2M-3M 168
3M-4M 0
5M-6M 7
6M-7M 7
>7M 63

请高手帮忙看看。

[feng2]

在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的数据。

[cyberwjw]

我可以把CCATSTEP_TBL_REP_ENDOFFICE b去掉，直接使用ccatstep_tbl_montst_statisc一个表还是不会得到我想要的结果

[cyberwjw]

更正下，得到结果是
既需要得到新的结果：
MAXRATE LINE_COUNT
<1M 0 --先前忘记把这条写上
1M-2M 28
2M-3M 168
3M-4M 0
5M-6M 7
6M-7M 7
>7M 63

[cyberwjw]

再次更正下，得到结果是
既需要得到新的结果：
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

[wiler]

with
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

[xiaoxiao1984]

select a.MAXRATE, nvl(b.line_count) 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

[xiaoxiao1984]

nvl(b.line_count,0) 漏写完整了
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