17,082
社区成员
发帖
与我相关
我的任务
分享
SELECT
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
--如果你的需求是像我的例子的话
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 6
SQL> 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
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)