时间戳

首页 > 解决方案 > 在 SQL 中使用内连接的递归 CTE

sql - 在 SQL 中使用内连接的递归 CTE

问题描述

我必须制作一个递归 CTE 以将所有知道 kevin bacon 的演员还给我，以便更好地了解我的数据库一个只通过两个演员了解培根的人 例如，您可能想知道阿尔弗雷德希区柯克如何与凯文培根联系起来。一个答案是：阿尔弗雷德·希区柯克和奥森·威尔斯一起在演艺界（1943），奥森·威尔斯和杰克·尼科尔森在一个安全的地方（1971），杰克·尼科尔森和凯文·培根在几个好男人（1992） ！我正在尝试两种不同的方法，一种是给我一个空集，另一种是给我这个错误消息'递归通用表表达式 DBMASTER 的完整选择。培根”

WITH bacon (actorid, bacon_number) AS (
SELECT UNIQUE actorid, 0 FROM movies2actors 
        WHERE actorid =  (SELECT actorid FROM actors WHERE name = 'Bacon, Kevin (I)') UNION ALL
SELECT movies2actors.actorid, bacon.bacon_number + 1
FROM movies2actors, bacon 
       WHERE movies2actors.actorid IN 
            (SELECT UNIQUE actorid FROM movies2actors WHERE movieid IN (SELECT UNIQUE movieid FROM movies2actors 
                WHERE actorid = (SELECT actorid FROM actors WHERE name = 'Bacon, Kevin (I)') )) 
            AND movies2actors.actorid <> (SELECT actorid FROM actors WHERE name = 'Bacon, Kevin (I)') AND bacon.bacon_number<2  
)
SELECT bacon.actorid , bacon.bacon_number  FROM bacon ;

WITH bacon (actorid,relationid, bacon_number) AS (
SELECT UNIQUE actorid, actorid ,0 FROM ACTORS 
        WHERE name = 'Bacon, Kevin (I)'
UNION ALL
SELECT ACTORS.actorid,bacon.relationid, bacon.bacon_number + 1
FROM ACTORS
       JOIN  bacon ON ACTORS.actorid = bacon.relationid
       WHERE ACTORS.actorid IN 
            (SELECT UNIQUE actorid FROM movies2actors WHERE movieid IN (SELECT UNIQUE movieid FROM movies2actors 
                WHERE actorid = (SELECT actorid FROM actors WHERE name = 'Bacon, Kevin (I)') )) 
            AND ACTORS.actorid <> (SELECT actorid FROM actors WHERE name = 'Bacon, Kevin (I)') AND bacon.bacon_number<2     
)
SELECT bacon.actorid , bacon.bacon_number  FROM bacon ;

标签： sqlrecursionjoindb2recursive-cte

解决方案

在递归 CTE 中使用旧的连接语法（在 Db2 LUW 中）

table a,
table b
Where a.col = b.col

推荐阅读