sql:
select count(ID) CNT,TYPEGROUP from(
select ID,
case when (COL_A>0 and COL_A<= 10) then 1 when (COL_A>10 and COL_A<=20) then 2
when (COL_A>20 and COL_A<=30) then 3 when (COL_A>30 and COL_A<=40) then 4
when (COL_A>40 and COL_A<=50) then 5 when (COL_A>50 and COL_A<=60) then 6
when (COL_A>60 and COL_A<=70) then 7 when (COL_A>70 and COL_A<=80) then 8
when (COL_A>80 and COL_A<=90) then 9 when (COL_A>90 and COL_A<=100) then 10
when (COL_A>100 and COL_A<=110) then 11 when (COL_A>110 and COL_A<=120) then 12
when (COL_A>120 and COL_A<=130) then 13 when (COL_A>130) then 14 end TYPEGROUP
from T_TEST
) group by TYPEGROUP
order by TYPEGROUP结果是这样的:
CNT TYPEGROUP
11 1
32 2
15 3
34 4
32 6
24 7
53 8
42 9
我希望能把其中的5,10,11,12,13,14都显示出来,只不过CNT为空或0
select count(ID) CNT,TYPEGROUP from(
select ID,
case when (COL_A>0 and COL_A<= 10) then 1 when (COL_A>10 and COL_A<=20) then 2
when (COL_A>20 and COL_A<=30) then 3 when (COL_A>30 and COL_A<=40) then 4
when (COL_A>40 and COL_A<=50) then 5 when (COL_A>50 and COL_A<=60) then 6
when (COL_A>60 and COL_A<=70) then 7 when (COL_A>70 and COL_A<=80) then 8
when (COL_A>80 and COL_A<=90) then 9 when (COL_A>90 and COL_A<=100) then 10
when (COL_A>100 and COL_A<=110) then 11 when (COL_A>110 and COL_A<=120) then 12
when (COL_A>120 and COL_A<=130) then 13 when (COL_A>130) then 14 end TYPEGROUP
from T_TEST
) group by TYPEGROUP
order by TYPEGROUP结果是这样的:
CNT TYPEGROUP
11 1
32 2
15 3
34 4
32 6
24 7
53 8
42 9
我希望能把其中的5,10,11,12,13,14都显示出来,只不过CNT为空或0
解决方案 »
- Sqlserver还原数据库时,日志文件过大,如何减少
- 类型转换/
- 初学powerdesigner,生成数据库遇到问题
- 急,SQL语句高手请进,在线等。
- 建立这样一个表用户表怎么建立是最好的?
- 数据库 未处理订单
- 这个查询怎么做啊,很急的,谢谢
- 打扰一下 想请教你个问题 sql里面有个字段pay_memo是存放ip地址的如221.199.137.218!001 我想请教一个select语句 只选出221.199.137.218
- 哎,没办法只好来麻烦大家了,如何用sql语句求出不重复字段为最大的纪录?
- 事务处理
- 高手请指教,SQL SERVER 2000内存占用问题
- 存储过程问题,条件 in语句 报错,请帮忙!
(select TYPEGROUP=1 union all select 2
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
union all select 10
union all select 11
union all select 12
union all select 13
union all select 14)a left join
(select ID,
case when (COL_A>0 and COL_A <= 10) then 1
when (COL_A>10 and COL_A <=20) then 2
when (COL_A>20 and COL_A <=30) then 3
when (COL_A>30 and COL_A <=40) then 4
when (COL_A>40 and COL_A <=50) then 5 when (COL_A>50 and COL_A <=60) then 6
when (COL_A>60 and COL_A <=70) then 7 when (COL_A>70 and COL_A <=80) then 8
when (COL_A>80 and COL_A <=90) then 9 when (COL_A>90 and COL_A <=100) then 10
when (COL_A>100 and COL_A <=110) then 11 when (COL_A>110 and COL_A <=120) then 12
when (COL_A>120 and COL_A <=130) then 13 when (COL_A>130) then 14 end TYPEGROUP
from T_TEST)b on a.TYPEGROUP=b.TYPEGROUP group by a.TYPEGROUP
order by a.TYPEGROUP
--借助spt_values 表
select count(1) as CNT,r.number as TYPEGROUP
from master..spt_values r left join [tb] t
on r.number = ceiling(t.COL_A/10.0)
where r.type = 'P'
and r.number >= 1 and r.number <= 14
group by r.number