Find duplicate / duplicate rows in hierarchical sql

Question

I am trying to detect duplicate / duplicate values ​​in a hierarchical table.

Consider the following (slightly contrived) example:

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

I really want...

     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

those. I want each row to be returned as many times as it exists in the subquery (ignoring the order). Because START WITH uses the IN clause, duplicate values ​​are suppressed. Is it possible to reorganize SQL so that I can do this?

Please note that in my case, the subposition is not UNION, but SELECT, which can return several (possibly duplicate) values ​​from the table.

I could do this in PL / SQL by writing the values ​​to the temp table and then GROUPING + COUNTing, but I would prefer to do this only in SQL, if possible.

, - .

: -)

EDIT:

, 0... N, .

Answer 1

...

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

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);

Answer 3

, emp , .

- , :

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;

Answer 4

it's pretty simple:

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;