select type,count(*) from tab group by type字段:type有4个值 分别是1,2,3,4
当查询的各个type值都有数据时是这样的type count(*)
1 100
2 200
3 300
4 400当查询的某个type值没有数据时是这样的
type count(*)
1 100
2 200
4 400这里面type为3的值为0,现在我想让它这样显示
type count(*)
1 100
2 200
3 0
4 400
不知道应该怎么写,请各位高手指教。
当查询的各个type值都有数据时是这样的type count(*)
1 100
2 200
3 300
4 400当查询的某个type值没有数据时是这样的
type count(*)
1 100
2 200
4 400这里面type为3的值为0,现在我想让它这样显示
type count(*)
1 100
2 200
3 0
4 400
不知道应该怎么写,请各位高手指教。
zz.type,
decode(max(tt.type) over(partition by zz.type),null,0,0,0,count(*) over(partition by zz.type)) as "count"
from (
select 1 as type,'aaa' as name from dual
union all
select 2 as type,'bbb' as name from dual
union all
select 3 as type,'ccc' as name from dual
union all
select 4 as type,'ddd' as name from dual
union all
select 5 as type,'eee' as name from dual
)zz,
(
select 101 as ANO,1 as type,105 as QTY from dual
union all
select 101 as ANO,2 as type,42 as QTY from dual
union all
select 101 as ANO,3 as type,25 as QTY from dual
union all
select 101 as ANO,4 as type,104 as QTY from dual
union all
select 204 as ANO,3 as type,61 as QTY from dual
union all
select 256 as ANO,1 as type,241 as QTY from dual
union all
select 256 as ANO,2 as type,91 as QTY from dual
union all
select 345 as ANO,1 as type,141 as QTY from dual
union all
select 345 as ANO,2 as type,18 as QTY from dual
union all
select 345 as ANO,4 as type,74 as QTY from dual
union all
select 620 as ANO,4 as type,125 as QTY from dual
)tt
where zz.type = tt.type(+);
===========================result============================= TYPE count
---------- ----------
1 3
2 3
3 2
4 3
5 0
from tab,type_tab
where tab.type(+)=type_tab.type
group by type_tab.type
---------- ---------- ----------
101 1 105
101 2 42
101 3 25
101 4 104
204 3 61
256 1 241
256 2 91
345 1 141
345 2 18
345 4 74
620 5 12511 rows selected
=========================sql========================== select distinct zz.type,
decode(max(tt.type) over(partition by zz.type),null,0,0,0,count(*) over(partition by zz.type))
from (select *
from AB
where ANO <> 620
)tt,
(
select distinct type
from AB
)zz
where zz.type = tt.type(+);
========================result======================== TYPE DECODE(MAX(TT.TYPE)OVER(PARTIT
---------- ------------------------------
1 3
2 3
3 2
4 2
5 0
楼主的意思是这个表根本没有type = 3的record,也就是你demo中 “620 5 125”这行,但是从业务上知道存在这种type = 3,所以结果中需要“3 0”这样的数据。最好的方法是要有一个表专门记录有几种type,然后用outer join,不然只有用hard code使结果存在这种type = 3。
例如(很蠢的方法,懒得去想其他方法=.=):
select 1, count(1) from tab where tab.type = 1
union all
select 2, count(1) from tab where tab.type = 2
union all
select 3, count(1) from tab where tab.type = 3
union all
select 4, count(1) from tab where tab.type = 4
...业务上存在几种type就写几个union all。不然这种type的数据一条没有的时候,是无法从table中获得这种缺失的type的。
请先看清楚需求再说...
呵呵,不要激动,你的回答我仔细看过了,但是楼主如果要的是要将表中根本没出现过的那种type的count得出来,那你的解法就无法实现了...
请先看清楚需求再说...
====================================================================呵呵,XD, 如果真的有没出现的type也要count出来,那不是毫无意义的需求?就算写出来了(只能union all)具有通用性吗?举一反三的道理应该明白吧 ...而且好像LZ说了有1、2、3、4四个值的哦,那就是说这个值还是存在的吧,只是where的时候把它过滤掉了 ...呵呵,我也是个菜鸟,等其他高手的回复 ... :-)