各位有碰到过这种情况吗?SELECT
COUNT(*) AS CASE_COUNT
, tb004.FILD5
FROM tb006
, tb004
WHERE tb006.FILD1 = '1'
AND tb006.FILD2 = '11'
AND tb006.FILD3 = '223'
AND tb006.FILD4 = '00'AND tb006.FILD1 = tb004.FILD1
AND tb006.FILD2 = tb004.FILD2
AND tb006.FILD3 = tb004.FILD3
AND tb006.FILD4 = tb004.FILD4
AND tb006.FILD5 = tb004.FILD5
group by tb004.FILD5有数据的时候结果很正常,没数据的时候它不返回0,返回空,我用decode转都没用,求教~~~
能帮我我返回0吗?
分不多,昨天押宝全输了。
COUNT(*) AS CASE_COUNT
, tb004.FILD5
FROM tb006
, tb004
WHERE tb006.FILD1 = '1'
AND tb006.FILD2 = '11'
AND tb006.FILD3 = '223'
AND tb006.FILD4 = '00'AND tb006.FILD1 = tb004.FILD1
AND tb006.FILD2 = tb004.FILD2
AND tb006.FILD3 = tb004.FILD3
AND tb006.FILD4 = tb004.FILD4
AND tb006.FILD5 = tb004.FILD5
group by tb004.FILD5有数据的时候结果很正常,没数据的时候它不返回0,返回空,我用decode转都没用,求教~~~
能帮我我返回0吗?
分不多,昨天押宝全输了。
如果是0的话,就别加group by了
http://topic.csdn.net/u/20101025/09/752283dc-d89a-410e-9db6-b3d6bd7f80d2.html?303153174
SELECT
COUNT(nvl(tb004.FILD5,0)) AS CASE_COUNT
, nvl(tb004.FILD5,0)
FROM tb006
, tb004
WHERE tb006.FILD1 = '1'
AND tb006.FILD2 = '11'
AND tb006.FILD3 = '223'
AND tb006.FILD4 = '00'AND tb006.FILD1 = tb004.FILD1
AND tb006.FILD2 = tb004.FILD2
AND tb006.FILD3 = tb004.FILD3
AND tb006.FILD4 = tb004.FILD4
AND tb006.FILD5 = tb004.FILD5
group by nvl(tb004.FILD5,0)
SQL> select dname,count(ename)
2 from emp
3 ,dept
4 where emp.deptno=dept.deptno
5 group by dname;DNAME COUNT(ENAME)
-------------- ------------
ACCOUNTING 3
RESEARCH 5
SALES 6SQL> ed
已写入 file afiedt.buf 1 select dname,count(ename)
2 from emp
3 ,dept
4 where emp.deptno(+)=dept.deptno
5* group by dname
SQL> /DNAME COUNT(ENAME)
-------------- ------------
ACCOUNTING 3
NANCHANG 0
OPERATIONS 0
RESEARCH 5
jiujiang 0
SALES 6
nanchang 0已选择7行。
--你可以用下面的方法试试
SELECT
COUNT(*) AS CASE_COUNT
, tb004.FILD5
FROM tb006
, tb004
WHERE tb006.FILD1 = '1'
AND tb006.FILD2 = '11'
AND tb006.FILD3 = '223'
AND tb006.FILD4 = '00'AND tb006.FILD1 = tb004.FILD1
AND tb006.FILD2 = tb004.FILD2
AND tb006.FILD3 = tb004.FILD3
AND tb006.FILD4 = tb004.FILD4
AND tb006.FILD5(+) = tb004.FILD5
group by tb004.FILD5
的值。但是,当tb004.FILD5为空时,也就是说你在以一个空来分组,那结果就相当于一个数除以0,是一个无限值。这个时候你无论怎么count都是没有意义的不知道这样理解对不对。
tb004.FILD5,
sum(case when tb006.FILD1 = tb004.FILD1
AND tb006.FILD2 = tb004.FILD2
AND tb006.FILD3 = tb004.FILD3
AND tb006.FILD4 = tb004.FILD4
AND tb006.FILD5 = tb004.FILD5 then 1 else 0 end)CASE_COUNT
FROM tb006 , tb004
where tb006.FILD1 = '1'
AND tb006.FILD2 = '11'
AND tb006.FILD3 = '223'
AND tb006.FILD4 = '00'
group by tb004.FILD5
不知道行不行
tb006.FILD1 = '1'
AND tb006.FILD2 = '11'
AND tb006.FILD3 = '223'
AND tb006.FILD4 = '00' ,它的分组就能实现,哪怕分组结果都是零 tb004.FILD5 num
-------------- ------------
00 0
01 0
77 0
02 0但是,当你一条数据都没有的时候,是count不出来的。
所以你的方法还是很有效的!!