在SQLServer中执行下面的语句:
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 as 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请高手帮忙看看。
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 as 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请高手帮忙看看。
解决方案 »
- 请问哪里能下载到SQL 2005 ssis 工具
- 菜鸟我被这个鬼存储过程搞的要挂了
- SQL 速度
- 邹建书《SQL Server 2000 开发与管理应用实例》广州哪有卖啊,谢谢啊~~~~我要~~~~~~~
- sql function 相关
- 紧急求救!SQL2000数据库一字段被删除,求教数据恢复!在线等
- 简单的问题。
- 很常见的问题,不知道怎么解决,求方法!
- [求助]SQL数据库搜索怎么提高效率
- 怎样才能查询出数据库中的指定数量的纪录阿,例如,表里一共有1000条纪录,我要的数据是从第30到第50条的数据,用什么语句能查出这20条记
- SQL Server2000启动停止问题!
- 统计Table_1在Table_2中没有的记录,是不是可以这样写?
既需要得到新的结果:
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
MAXRATE
1M-2M
2M-3M
3M-4M
5M-6M
6M-7M
>7M你上面的结果right join 这张表。
<1M
1M-2M
2M-3M
3M-4M
4M-5M --先前又忘记把这条写上
5M-6M
6M-7M
>7M
sum(case when Ratedn_Max>=1024 and Ratedn_Max<2048 then count(Ratedn_Max) else 0 end ) [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'
union all
select '2M-3M' [MAXRATE],
sum(case when Ratedn_Max>=2048 and Ratedn_Max<3072 then count(Ratedn_Max) else 0 end ) [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'以此类推,可能动态语句好一些。
的说法
(MAXRATE varchar(10),
LINE_COUNT int
)
insert aaa
select '1M-2M', 28 union all
select '2M-3M', 168 union all
select '5M-6M', 7 union all
select '6M-7M', 7 union all
select '>7M', 63
select * from aaa
create table bbb
(
MAXRATE varchar(10)
)
i
nsert bbb
select '<1M' union all
select '1M-2M' union all
select '2M-3M' union all
select '3M-4M' union all
select '4M-5M' union all
select '5M-6M' union all
select '6M-7M' union all
select '>7M'
select * from bbbselect bbb.maxrate,isnull(line_count,0) as line_count from bbb left join aaa on bbb.maxrate=aaa.maxrate
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