sql - 查找必须搜索多少次表才能获得匹配的 Oracle 数据库

Question

我在 oracle9i 的数据库中有这个表:

CREATE TABLE involved_in
(
    rid number,
    aid number NOT NULL,
    fid number NOT NULL,
    role varchar(80),
    note varchar(80), 
    job varchar(35),
    PRIMARY KEY(rid),
    FOREIGN KEY(fid) REFERENCES production(pr_id),
    FOREIGN KEY(aid) REFERENCES person(pid)
);

其中包含有关在电影 (fid) 中工作的 Actor (aid) 的数据。

我想做的类似于计算 Bacon number

除了我的 kevin bacon 以拥有援助 517635 而闻名。

我不知道如何计算(仅使用 SQL 语句)我必须将给定 Actor (通过另一个助手)连接到 517635 的 Actor 数量。

查询的结果要么是给定 Actor 必须联系的所有 Actor 的列表，要么只是一个数字。

为此，我认为我必须首先获得所有与 517635 合作过的 Actor ，而那些直接与他合作过的 Actor 将获得 1。 table 不是太大，但足够大，可以做到这一点。

例如，假设布拉德皮特是我的 517635。他与安吉丽娜朱莉合作过史密斯夫妇，这将使她成为第一名。如果布拉德皮特从未与布鲁斯威利斯合作过任何电影(假设是这种情况) 但安吉丽娜朱莉与他合而为一，那么布鲁斯威利斯相对于布拉德皮特的数字将是 2。

在我的查询中，如果给定的号码是 Angelina 的，结果将是:“布拉德皮特 1”或简称“1”如果给定的数字是威利斯的，结果将是:“安吉丽娜·朱莉 布拉德皮特 2"要么“2”表中内容的示例:

INSERT INTO involved_in(rid, aid, fid, role, note, job) VALUES(1, 33,                  1584953, 'Himself', 'NULL', 'actor');
INSERT INTO involved_in(rid, aid, fid, role, note, job) VALUES(2, 1135, 1999660, 'Himself', 'NULL', 'actor');
INSERT INTO involved_in(rid, aid, fid, role, note, job) VALUES(3, 1135, 2465724, 'Himself', 'NULL', 'actor');
INSERT INTO involved_in(rid, aid, fid, role, note, job) VALUES(4, 6003, 2387806, 'Himself', '(archive footage)', 'actor');
INSERT INTO involved_in(rid, aid, fid, role, note, job) VALUES(5, 13011, 1935123, 'Himself', 'NULL', 'actor');

我脑子里什么都没有，我是 SQL 的新手，我能想到的所有内容都会导致无限循环，并使用一个变量来计算循环次数。关于从哪里开始以及幸运的话结束的任何想法？

Answer 1

如果没有示例数据，这很难验证，但以下答案至少在句法上是正确的。

你在这里想要的显然是一个递归查询。在 Oracle 中有多种方法可以做到这一点:使用公用表表达式 (CTE)(即 with 子句)；或使用 connect by条款。 CTE 是 SQL 标准和 connect by是专有的，但我发现 connect by不那么令人困惑，所以这就是我将使用的(9i 中也不支持递归 CTE)。

SELECT   aid, MIN (lvl) AS distance
FROM     (SELECT     ii2.aid, LEVEL AS lvl
          FROM       involved_in ii1
                     JOIN involved_in ii2
                        ON ii1.fid = ii2.fid AND ii1.aid <> ii2.aid
          START WITH ii1.aid = 517635
          CONNECT BY NOCYCLE ii1.aid = PRIOR ii2.aid)
GROUP BY aid

• Join 连接所有 aid共享 fid对彼此。
• connect by子句加入每组连接 aid到另一组 aid .
• nocycle子句防止无限递归。
• level是给我们递归发生次数的关键字。我们必须得到最小值，因为任何给定的 aid可以连接到起始 aid通过多条路径。
• 如果此查询执行得非常糟糕，您可能希望只获取特定距离内的结果。最好的方法是添加 and level <= 100到 connect by子句(在这种情况下，将结果限制在 100 或更小的距离内)。

---

正如评论中所指出的，9i 不支持 nocycle .此外，OP 在运行此查询时用完了临时空间。两者其实是没有关系的:如果出现循环，Oracle会抛出错误；这意味着服务器在找到循环之前用完了临时空间。我看不到解决这两个问题的任何好方法。

可以指定一个终点(在某种程度上)。您可以添加 AND ii1.aid <> 2其中 2是 aid on 的终点条款。这将导致查询在遇到该值时停止导航分支。然而，这可能无助于解决上述问题，因为它只会使找到所提供值的那些分支短路。它仍然必须评估所有其他分支，以防值在它们的某个地方。