查找所有可能路径的SQL查询

Question

我有这个表格：

create table testtb (c1 number, c2 number);
insert into testtb values (1, 100);
insert into testtb values (2, 100);
insert into testtb values (3, 100);
insert into testtb values (3, 101);
insert into testtb values (4, 101);
insert into testtb values (5, 102);
commit; 

我很难想出一条SQL查询语句，使得当where子句为"c2=100"时返回以下结果：

结果集：

c1 c2
-- ---
1  100
2  100
3  100
3  101
4  101

结果集包含“3,101”的原因是因为它可以通过“3,100”到达。同样，“4,101”也是如此：可以通过“3,101” -> “3,100”到达。

更新：此表包含来自2个不同数据集的标识符，经过相似性匹配后得出。因此，想要允许用户通过任何标识符进行搜索，并显示两个数据集之间所有可能的匹配项。这就是为什么当用户搜索“c2 = 100”时，我还想显示“3,101”和“4,101”，以显示匹配项的完整图形。

谢谢。

- Timour

---

你能再解释一下这些行代表什么吗？为什么第3,100行要将另外两个项目添加到结果中？ - Chris Nash

也许你可以把你现在的代码给我们看一下，这样我们就能知道你想要做什么了。 - Stefan H

你是否在寻找类似于 CONNECT BY 的东西？层级数量是否有限制 - 大概至少需要2个层级才能得到 4,101 条目。但是不太清楚你需要什么... - Alex Poole

@Alex Poole：那是我第一次尝试使用“connect by”，但我无法弄清楚如何使用。这不是“父子”关系。递归级别未知，但我不希望它超过5。 - Timour

Answer 1

select distinct c1, c2
from testtb
connect by nocycle prior c1 = c1 or prior c2 = c2
start with c2 = 100
order by c1, c2;

- Jon Heller

---

这就是全部内容了！如果您不介意，我还有一个问题。针对测试表（包含两个列索引）的此查询的执行计划显示“全表扫描”。这是因为“connect by”吗？ - Timour

很可能与connect by有关，我曾经遇到过这些查询类型的性能问题。我添加了10万行，在C1和C2上建立了索引并进行了统计，11gR2只使用了C2索引。奇怪的是，connect by查询中的全表扫描基数估计为2(!)，但对于一个简单的select count(*) from testtb命令，基数估计是完美的。我能通过添加提示“/*+ dynamic_sampling(testtb, 4) */”来使用两个索引。 - Jon Heller

谢谢，我会尝试使用提示针对真实数据运行查询，并查看结果如何。 - Timour

Answer 2

与jonearles的答案相同，但使用递归子查询因子：

  WITH pathtb(c1,c2) AS
  (
  SELECT c1,c2 FROM testtb WHERE c2=100
  UNION ALL
  SELECT testtb.c1,testtb.c2 FROM
     testtb JOIN pathtb ON (pathtb.c1=testtb.c1 or pathtb.c2=testtb.c2)
  ) CYCLE c1,c2 set cycle TO 1 default 0
  SELECT DISTINCT c1,c2 FROM pathtb WHERE cycle=0
  ORDER BY c1,c2

- Mike

---

+1 好查询。它看起来比connect by更复杂，但是您的查询更标准且运行速度快几倍。 - Jon Heller

同意，这是一个不错的查询，但是Oracle (10/11)g不支持这种语法 :( - Timour

Answer 3

尝试使用子查询... 我从您的初始帖子中推断出这一点，希望对您有所帮助。

select * from testtbl where c1 in (select c1 from testtbl where c2=100)

我是一个MSSQL人，所以如果这与PL-SQL不完全匹配，请见谅，但您可以理解我的意思。

编辑： 抱歉，我看到您还想要4,101。也许需要两个子查询级别？

    select *
    from testtbl
    where c2 in
    (select c2 from testtbl where c1 in (select c1 from testtbl where c2=100))

- pelazem