在分层 SQL 中查找重复行/重复项

Question

我将尝试在分层表格中检测重复值。

考虑以下（有点牵强的）示例：

SELECT *
FROM   emp
START WITH mgr IN (SELECT empno FROM emp WHERE ename = 'JONES'
                   UNION ALL
                   SELECT empno FROM emp WHERE ename = 'JONES')
CONNECT BY PRIOR empno = mgr;

Returns...

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

我需要的是...

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

我希望每一行都能按照子查询中它的存在次数返回（忽略顺序）。由于START WITH使用了IN子句，重复的值被压制了。是否可以重新组织SQL以便我可以做到这一点？

请注意，在我的情况下，子句不是UNION，而是SELECT，可能会从表中返回多个（可能重复的）值。

如果可能的话，我更喜欢仅在SQL中完成，虽然我可以通过将值写入临时表中并进行GROUP + COUNT来完成PL/SQL。

如果需要任何澄清，请告诉我。

谢谢 :-)

编辑：

请注意，子查询可能返回0...N个值。

- cagcowboy

Answer 1

尝试这个...

SELECT  EMPNO,ENAME FROM,count(*)as counts   emp group by EMPNO,ENAME having count(*)>1

- John

---

我会说这是做这种事情的典型方式，使用“having” SQL关键字。 - djangofan

Answer 2

复制结果集的一种方法是将其与具有两行的结果集进行交叉连接（笛卡尔积），即：

SQL> WITH your_query AS (
  2     SELECT object_name
  3       FROM all_objects WHERE ROWNUM <= 3
  4  )
  5  SELECT your_query.*
  6    FROM your_query
  7   CROSS JOIN (SELECT NULL FROM dual UNION ALL SELECT NULL FROM dual);

OBJECT_NAME
------------------------------
IND$
IND$
ICOL$
ICOL$
OBJ$
OBJ$

在你的情况下，这应该可以工作：

WITH your_query AS (
   SELECT *
     FROM emp
    START WITH mgr IN (SELECT empno FROM emp WHERE ename = 'JONES')
          CONNECT BY PRIOR empno = mgr
)
SELECT your_query.*
  FROM your_query
 CROSS JOIN (SELECT NULL FROM dual UNION ALL SELECT NULL FROM dual);

- Vincent Malgrat

Answer 3

看起来你需要先从 emp 表与你的复杂查询结果进行外连接，然后基于此进行 connect-by 查询。

可能像这样：

WITH mgrs AS (
   SELECT empno FROM emp WHERE ename = 'JONES'
   UNION ALL
   SELECT empno FROM emp WHERE ename = 'JONES'
),
all_emps AS (
   SELECT emp.*, 
          CASE WHEN mgrs.empno IS NOT NULL THEN 1 END AS start_with  
   FROM emp
   LEFT OUTER JOIN mgrs ON mgrs.empno = emp.mgr
)
SELECT *
FROM all_emps
START WITH start_with = 1
CONNECT BY PRIOR empno = mgr;

- CaptainPicard

Answer 4

这很容易：

SELECT *FROM empSTART WITH mgr IN (SELECT empno FROM emp WHERE ename = 'JONES' UNION ALL
SELECT empno FROM emp WHERE ename = 'JONES')CONNECT BY PRIOR empno = mgr;

- PassionateDeveloper

---

UNION ALL 无法解决此问题...在评估 IN 时，重复的值将被抑制。 - cagcowboy