SQL好友列表查询 [英] SQL Friends List Query

问题描述

我是这种数据库SQL语言的新手，所以我将尝试使其尽可能简单. (我正在使用MySQL Workbench)

I am new to this database SQL language so I will try to make it as simple as possible. (I am using MySQL Workbench)

我有一个用于用户的表，我希望多个用户彼此成为朋友.为此，我创建了friends表，其中有一个user_id和friend_user_id.假设我们有(1,2)，(2,3).我希望将其理解为:"2是1和3的朋友，1是2的朋友，3是2的朋友".因此，在此好友表上插入时，我永远不会做类似(1,2)，(2,1)的操作.我正在寻找一个过程，该过程通过接收user_id作为参数来返回他所有的朋友，无论他们是在user_id列还是在friend_user_id列中.例如，如果我寻找用户2的朋友，则应该在1列中显示1和3，因为1和3是2的朋友.

I have a table for User and I want multiple users to be friends with each other. For that I created the friends table where it has a user_id and friend_user_id. So let's say we have (1,2), (2,3). I want this to be read as: "2 is friends with 1 and 3, 1 is friends with 2, 3 is friends with 2". So when inserting on this friends table I never do something like this (1,2),(2,1). I'm looking for a procedure that by receiving an user_id as parameter to return all his friends whether they are in the user_id column or the friend_user_id column. For example, if I look for user 2's friends it should appear 1 column with 1 and 3, because 1 and 3 are friends with 2.

更具体地说，当我调用get_friends(2)时，它应该出现

To be more specific, when I call get_friends(2) it should appear

[1]
[3]

即使它们在friends表的不同列中.

Even though these are in different columns on the friends table.

推荐答案

您可以使用IN检查任一列是否等于您要查找的用户的ID，并使用CASE ... END来获取该列这不等于您要查找的用户的ID.

You can use INto check if either column is equal to the ID of the user you want to look up and a CASE ... END to take the column which is not equal to the ID of the user you want to look up.

SELECT CASE
         WHEN user_id = 2
           THEN user_friend_id
         WHEN user_friend_id = 2
           THEN user_id
       END friend
       FROM friends
       WHERE 2 IN (user_id, user_friend_id);

或者，您可以使用UNION ALL方法，由于它可以使用user_id或user_friend_id上的索引，因此可能会更好地执行.

Alternatively you could use a UNION ALL approach, which might perform better as it can use indexes on user_id or user_friend_id.

SELECT user_id friend
       FROM friends
       WHERE user_friend_id = 2
UNION ALL
SELECT user_friend_id friend
       FROM friends
       WHERE friend_id = 2;

但是，只有在有这样的索引的情况下，这才更好.如果没有，则将需要在表上进行两次扫描，而第一种方法则只需要进行一次扫描.因此，在这种情况下更糟.

But this is only better if there are such indexes. If there aren't, it will need two scans on the table opposed to the first approach only needing one. So it's worse in that case.

这篇关于SQL好友列表查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！