查询以显示共同的朋友 [英] query to display mutual friends

问题描述

我有一个表friend_master，用于存储用户ID和朋友ID.
现在，我想在我的项目中的GridView中显示共同的朋友.

我想要SQL查询，有人可以帮忙吗?

在高级中表示感谢.

I have table friend_master where I store user''s id and friend''s id.
Now I would like to display mutual friends in my project in GridView.

I would like the SQL query for that, can any one help me out?

Thanks in advanced.

推荐答案

以下是用于获取以下内容的SQL语句:
-两个特定人的共同朋友
-所有人员以及与其他人员的共同朋友的总列表.

为了进行测试，我添加了表创建和数据插入语句.

表创建:

Below the are the SQL statements for getting:
- The mutual friends of two specific persons
- Total list of all persons and the mutual friends they have with the other persons.

For testing I added table creation and data insertion statements.

Table creation:

CREATE TABLE dbo.Person
(ID INT PRIMARY KEY,
 Name VARCHAR(25))

CREATE TABLE dbo.Friendship
(PersonID INT,
 FriendID INT)

数据插入:

Data insertion:

INSERT INTO dbo.Person VALUES ( 1, 'Person01')
INSERT INTO dbo.Person VALUES ( 2, 'Person02')
INSERT INTO dbo.Person VALUES ( 3, 'Person03')
INSERT INTO dbo.Person VALUES ( 4, 'Person04')

-- The Friendship table stores the friendship between two persons
-- in both directions. So the friendship between Person01 and
-- and Person02 is stored as (1, 2) and (2, 1). When inserting or
-- deleting friendships both record need inserting or removing.
INSERT INTO dbo.Friendship VALUES (1, 2)
INSERT INTO dbo.Friendship VALUES (1, 4)
INSERT INTO dbo.Friendship VALUES (2, 1)
INSERT INTO dbo.Friendship VALUES (2, 3)
INSERT INTO dbo.Friendship VALUES (2, 4)
INSERT INTO dbo.Friendship VALUES (3, 2)
INSERT INTO dbo.Friendship VALUES (3, 4)
INSERT INTO dbo.Friendship VALUES (4, 1)
INSERT INTO dbo.Friendship VALUES (4, 2)
INSERT INTO dbo.Friendship VALUES (4, 3)

Person01和Person02的共同朋友:

Mutual friends for Person01 and Person02:

SELECT P1.Name
FROM dbo.Friendship AS F1
JOIN dbo.Person AS P1 ON P1.ID = F1.FriendID
WHERE F1.PersonID = 1 AND
      F1.FriendID IN (SELECT F2.FriendID
                      FROM dbo.Friendship AS F2
                      WHERE F2.PersonID = 2)

所有人的共同朋友列表:

List of mutual friends for all persons:

SELECT P1.Name, P2.Name, P3.Name AS MutualFriend
FROM dbo.Friendship AS F1
JOIN dbo.Friendship AS F2 ON F2.PersonID <> F1.PersonID AND F2.FriendID = F1.FriendID
JOIN dbo.Person AS P1 ON P1.ID = F1.PersonID
JOIN dbo.Person AS P2 ON P2.ID = F2.PersonID
JOIN dbo.Person AS P3 ON P3.ID = F2.FriendID
ORDER BY F1.PersonID, F2.PersonID

这篇关于查询以显示共同的朋友的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！