Oracle SQL如何编写一条sql语句，以验证我的网络中的用户(即朋友还是朋友的朋友) [英] Oracle SQL how to write a sql statement that verifies if user in my network( ie friends or friend of friends)

问题描述

我有这个问题.给定一个users表，该表由社交网络中用户的用户名组成，而friends表包含一个用户名和一个用户的朋友名，如下所示...

I have this problem. Given a users table that consists of users' username in a social network and friends table that contain a user's name and a user's friendname like below...

username friendname

John        Thomas
Chris       James

...我正在尝试编写一条SQL语句，如果用户在我的网络中，它将执行该操作.换一种说法 该用户是朋友还是朋友的朋友?

... I'm trying to write an SQL statement that will if a user is in my network. In other words is that user a friend or friend of friends?

我一直在解决这个问题，只能提出以下查询:

I've been dancing around this problem and could only come up with this query:

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';    

它基本上检查用户是否是我朋友的朋友，即如果为假，则仅返回null.

It basically check if a user if is a friend of my friend i.e. just return null if false.

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

Trying to figure out how I can expand to go through all my network of friend. I mean not just friend of my friend.

推荐答案

SELECT  *
FROM    (
        SELECT  username
        FROM    friends
        START WITH
                username = 'myname'
        CONNECT BY
                friendname = PRIOR username
                AND level <= 3
        )
WHERE   username = 'friendname'
        AND rownum = 1

根据需要更新级别:您可以搜索第三层好友等.

Update the level as necessary: you may search for the third layer friends etc.

如果友谊关系是对称的，则应进行以下查询:

If the friendship relationship is symmetric, you should make the following query:

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

如果您对表进行非规范化，则可以使查询更快:每个友谊存储两个记录，如下所示:

This query can be made much faster if you denormalize your table: store two records per friendship, like this:

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:

Then you can just replace the CTE above with the 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

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

, which will use the index and be much more efficient, especially if you limit the level to some reasonable value.

这篇关于Oracle SQL如何编写一条sql语句，以验证我的网络中的用户(即朋友还是朋友的朋友)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！