现有一张表test含两个字段idA和idB,如何使用一个sql实现将两个字段所有有关联的id作为一个集合输出。
例:
idA idB
A B
B A
B D
D G
F H
F J
预期结果:
No id
1 A
1 B
1 D
1 G
2 F
2 H
2 J 使用:WITH xxx AS(
...
union all
...
)select * from xxx;时会出现大量重复数据,有没有什么解决办法?
例:
idA idB
A B
B A
B D
D G
F H
F J
预期结果:
No id
1 A
1 B
1 D
1 G
2 F
2 H
2 J 使用:WITH xxx AS(
...
union all
...
)select * from xxx;时会出现大量重复数据,有没有什么解决办法?
解决方案 »
- Spring Security 连接Oracle 找不到SID
- 请教:expected number got - 是什么意思
- 在oracle中如何批量修改字体中的某个词
- 将dbf文件导入到oracle数据库,急
- 如何用SQL Plus远程登陆到别的机器上的oracle数据库
- Quest的Knowledge Xpert for PLSQL Development问题
- 菜鸟求助:oracle的触发器问题:触发器无效且未通过重新确认
- 客户端连接Oracle服务器
- 为什么啊??
- 100分求SQL,自己整半天了
- 关于一个简单的触发器去字段末尾空格和回车的问题
- 打开绿色版sql developer时,加载完闪退真么回事.懂得扣我 2313133415 或者评论 谢谢
递归只能用union all
WITH TMP AS
(SELECT 'A' AS ID1, 'B' AS ID2
FROM DUAL
UNION ALL
SELECT 'B' AS ID1, 'A' AS ID2
FROM DUAL
UNION ALL
SELECT 'B' AS ID1, 'D' AS ID2
FROM DUAL
UNION ALL
SELECT 'D' AS ID1, 'G' AS ID2
FROM DUAL
UNION ALL
SELECT 'F' AS ID1, 'H' AS ID2
FROM DUAL
UNION ALL
SELECT 'F' AS ID1, 'J' AS ID2
FROM DUAL)
SELECT ID1, DENSE_RANK() OVER(ORDER BY MIN(R)) RN
FROM (SELECT ID1, ID2, CONNECT_BY_ROOT(ID1) R
FROM TMP T
CONNECT BY NOCYCLE PRIOR ID1 = ID2
UNION ALL
SELECT ID2, ID1, CONNECT_BY_ROOT(ID1) R
FROM TMP T
CONNECT BY NOCYCLE ID1 = PRIOR ID2)
GROUP BY ID1