我有这个问题.给定一个用户表,其中包含社交网络中的用户用户名和包含用户姓名和用户友好名称的朋友表,如下所示…
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.
试图找出我如何扩展以通过我所有的朋友网络.我的意思不仅仅是我朋友的朋友.
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
然后你可以用dual_friends替换上面的CTE：
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
,它将使用索引并且效率更高,特别是如果您将级别限制为某个合理的值.
