select b,decode(sign(a-2),-1,'0-2',decode(sign(a-4),-1,'2-4','5-8')) c ,count(*) d from table1 group by b,decode(sign(a-2),-1,'0-2',decode(sign(a-4),-1,'2-4','5-8')) c
Select b,Case When a<2 then '0-2' else when a<5 then '2-4' else when a<8 then '5-8' else '其它' end as www, Sum(Case When a<2 then '0-2' else when a<5 then '2-4' else when a<8 then '5-8' else '其它' end) as SumWWW from table1 Group by b,Case When a<2 then '0-2' else when a<5 then '2-4' else when a<8 then '5-8' else '其它' end
select b,decode(sign(a-2),-1,'0-2',decode(sign(a-4),-1,'2-4','5-8')) c ,nvl(count(1),0) d from table1 group by b,decode(sign(a-2),-1,'0-2',decode(sign(a-4),-1,'2-4','5-8')) c
to Tongls(编程杀手),case好象是Oracle10g的新功能吧,这边是Oracle 8i
这个表的设计有问题,所以才会造成语句难构造的! 如果把 C 分解成 C_min 和 C_max 就好解决了!
to hevin(刚出道,CODING),忘记了说明,b1,b2是不可知的,只有那几个区间是固定的
这样写就行了: select t.b,t.c,nvl(tt.d,0) d from (select distinct b,c from table1 ,(select '0-2' c from dual union select '2-4' c from dual union select '5-8' c from dual) ) t ,(select b,decode(sign(a-2),-1,'0-2',decode(sign(a-4),-1,'2-4','5-8')) c ,count(*) d from table1 group by b,decode(sign(a-2),-1,'0-2',decode(sign(a-4),-1,'2-4','5-8')) ) tt where t.b=tt.b(+) and t.c=tt.c(+) 先拿 distinct b值(m行)和可能的区间c值(n行)构造一个(m×n行)的表 再与那个包含count的表外连接关联,count为空的返回0就行了
to shotking(小金) 虽然用到了union,但还好没有union table1,差不多可以了,也想不出更好的方案了。谢谢!
,nvl(count(1),0) d
from table1 group by b,decode(sign(a-2),-1,'0-2',decode(sign(a-4),-1,'2-4','5-8')) c
b1 2-4
b1 5-8
b2 0-2
b2 2-4
b2 5-8
如果把 C 分解成 C_min 和 C_max 就好解决了!
select t.b,t.c,nvl(tt.d,0) d
from (select distinct b,c
from table1
,(select '0-2' c from dual
union
select '2-4' c from dual
union
select '5-8' c from dual)
) t
,(select b,decode(sign(a-2),-1,'0-2',decode(sign(a-4),-1,'2-4','5-8')) c
,count(*) d
from table1
group by b,decode(sign(a-2),-1,'0-2',decode(sign(a-4),-1,'2-4','5-8'))
) tt
where t.b=tt.b(+)
and t.c=tt.c(+)
先拿 distinct b值(m行)和可能的区间c值(n行)构造一个(m×n行)的表
再与那个包含count的表外连接关联,count为空的返回0就行了