小编典典

Oracle SQL如何编写一条sql语句,以验证我的网络中的用户(即朋友还是朋友的朋友)

sql

我有这个问题。给定一个users包含社交网络中friends用户名和用户名的表,该表包含用户名和用户的朋友名,如下所示…

username friendname

John        Thomas
Chris       James

…我正在尝试编写一条SQL语句,如果用户在我的网络中,它将执行此操作。换句话说,该用户是朋友还是朋友的朋友?

我一直在围绕这个问题跳舞,只能提出以下查询:

SELECT f2.username, f2.friendname 
FROM friends f2 
WHERE f2.username IN (
      SELECT f1.friendname 
      FROM friends f1 
      WHERE f1.username = 'Thomas') 
AND f2.friendname <> 'user1' 
AND f2.friendname = 'user2';

它基本上检查用户是否是我朋友的朋友,即如果为false则仅返回null。

试图弄清楚如何扩展才能遍及我的所有朋友网络。我的意思不仅是我朋友的朋友。


阅读 47

收藏
2021-04-07

共1个答案

小编典典

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

,它将使用索引并且效率更高,尤其是如果您将级别限制为某个合理值时。

2021-04-07