SELECT *
FROM (
SELECT username
FROM friends
START WITH
username = 'myname'
CONNECT BY
friendname = PRIOR username
AND level <= 3
)
WHERE username = 'friendname'
AND rownum = 1
根据需要更新级别：您可以搜索第三层好友等。
如果友谊关系是对称的，则应进行以下查询：
WITH q AS
(
SELECT username, friendname
FROM friends
UNION ALL
SELECT friendname, username
FROM friends
),
f AS
(
SELECT friendname, level
FROM q
START WITH
username = 'Thomas'
CONNECT BY NOCYCLE
username = PRIOR friendname
)
SELECT *
FROM f
WHERE friendname = 'Jo'
AND rownum = 1
如果您对表进行非规范化，则可以使查询更快：每个友谊存储两个记录，如下所示：
CREATE TABLE dual_friends (orestes NOT NULL, pylades NOT NULL, CONSTRAINT pk_dualfriends_op PRIMARY KEY (orestes, pylades)) ORGANIZATION INDEX
AS
SELECT username, friendname
FROM friends
UNION ALL
SELECT friendname, username
FROM friends
然后，您可以将CTE上述内容替换为dual_friends：
WITH f AS
(
SELECT pylades, level
FROM dual_friends
START WITH
orestes = 'Thomas'
CONNECT BY NOCYCLE
orestes = PRIOR pylades
AND level <= 3
)
SELECT *
FROM f
WHERE pylades = 'Jo'
AND rownum = 1
，它将使用索引并且效率更高，尤其是如果您将级别限制为某个合理值时。
602392714
清零编程群