需求:http://bbs.csdn.net/topics/390483676?page=1#post-394744861
想得到这5张表里 name字段相同的分别有几条数据
EMP 表:
EMP_COPY表:
EMP_BACK表:
WITH
TB1 AS (SELECT ENAME,COUNT(*) AS T1_COUNT FROM EMP WHERE ENAME IS NOT NULL GROUP BY ENAME),TB2 AS (SELECT ENAME,COUNT(*) AS T2_COUNT FROM EMP_COPY WHERE ENAME IS NOT NULL GROUP BY ENAME),
TB3 AS (SELECT ENAME,COUNT(*) AS T3_COUNT FROM EMP_BACK WHERE ENAME IS NOT NULL GROUP BY ENAME),
TB4 AS (SELECT TB1.ENAME FROM TB1 UNION ALL
SELECT TB2.ENAME FROM TB2 UNION ALL
SELECT TB3.ENAME FROM TB3 ),
TB5 AS (SELECT TB4.ENAME FROM TB4 GROUP BY TB4.ENAME)
SELECT TB5.ENAME,NVL(TB1.T1_COUNT,0) AS EMP_COUNT,NVL(TB2.T2_COUNT,0) AS EMP_COPY_COUNT,NVL(TB3.T3_COUNT,0) AS EMP_BACK_COUNT
FROM TB1,TB2,TB3,TB5
WHERE TB5.ENAME=TB1.ENAME(+) AND
TB5.ENAME=TB2.ENAME(+) AND
TB5.ENAME=TB3.ENAME(+);
结果为: