AB two sets of data, how to use a SQL implementation will be two fields all related data classified-CodePudding

A list of existing test contains two fields idA and idB, how to use a SQL implementation will be two fields all related id as a set of output,
Example:
IdA idB
A, B
A, B
B D
D G
F H
F J
Expected results:
No id
1 A
1 B
1 D
1 G
2 F
2 H
2 J

Use: AS WITH XXX (
.
Union all
.
) select * from XXX. A large number of duplicate data would appear, is there any solution?

CodePudding user response:

Don't use union all don't want to have a repeat it with the union

CodePudding user response:

reference 1st floor minsic78 response:

don't want to have a repeat don't use union all, with the union

Recursion can only use union all

CodePudding user response:

Are using DB2 data

CodePudding user response:

Well, use the DB2 database?

CodePudding user response:

Here is the Oracle to DB2 to ask next better,

CodePudding user response:

The

reference 5 floor qq646748739 reply:

here is Oracle, better to ask next to DB2,

There are few, let me try here,,,

CodePudding user response:

Help,,

CodePudding user response:

Don't know your zha recursive CTE, may recursive have a better way to

 WITH TMP AS 
(SELECT the 'A' AS ID1, 'B' AS ID2 
The FROM DUAL 
UNION ALL 
SELECT 'B' AS ID1, 'A' AS ID2 
The FROM DUAL 
UNION ALL 
SELECT 'B' AS ID1, 'D' AS ID2 
The FROM DUAL 
UNION ALL 
SELECT the 'D' AS ID1, 'G' AS ID2 
The FROM DUAL 
UNION ALL 
SELECT the 'F' AS ID1, 'H' AS ID2 
The FROM DUAL 
UNION ALL 
SELECT the 'F' AS ID1, 'J' AS ID2 
The FROM DUAL) 
SELECT ID1, DENSE_RANK () OVER RN (ORDER BY MIN (R)) 
The FROM (SELECT ID1, ID2, CONNECT_BY_ROOT (ID1) R 
The FROM TMP T 
CONNECT BY NOCYCLE the PRIOR ID1=ID2 
UNION ALL 
The SELECT ID2, ID1, CONNECT_BY_ROOT (ID1) R 
The FROM TMP T 
CONNECT BY NOCYCLE ID1=the PRIOR ID2) 
GROUP BY ID1

CodePudding user response:

Don't know your ID that a list of what is said