mysql数据库inner join_mysql – INNER JOIN表本身

我有一个表格,代表两个用户之间的友谊.每个条目都是单向的;友谊需要两个条目来表示.我想保持这种方式.

user1_id | user2_id

43 44

44 43

我很好奇查询这种设置的最佳方法是什么.例如,如何查询特定用户的所有朋友的列表？

我提出的解决方案是两个反转和内部将友谊表连接到自身,首先获得所有完整对的列表,然后使用正常的WHERE子句：

SELECT f.user2_id

FROM friendships f

INNER JOIN friendships f2

ON f.user1_id = f2.user2_id

&& f.user2_id = f2.user1_id

WHERE f.user1_id = 43;

为了进入多汁的情况,我还需要能够查询以获得由用户的朋友创建的线程列表.我仍然在适应使用连接所需的抽象思维,但我提出的解决方案是：

SELECT thread_id,owner_id,message,time

FROM threads th

INNER JOIN friendships f

ON th.owner_id = f.user2_id

&& f.user1_id = 43

INNER JOIN friendships f2

ON f.user2_id = f2.user1_id

&& f.user1_id = f2.user2_id

这似乎有效,但我正在一个非常空的数据库上测试它.由于我对连接的掌握仍然有点弱,我担心在某些情况下会出现不良后果.所以我的问题是：

>这两个查询的行为与我期望的一样吗？

>有没有更好的方法来解决这个问题？

解决方法:

让我们开始通过创建一个查询来回答这个问题,该查询给出了所有(用户,朋友)对的列表.你要求朋友对是互惠关系,所以我们需要这样做.否则友谊表将提供这些对.

您的查询非常接近.此查询所需的是内部联接.

SELECT f1.user1_id AS user,

f1.user2_id AS friend

FROM friendships AS f1

JOIN friendships f2

ON (f1.user1_id = f2.user2_id AND f1.user2_id = f2.user1_id)

然后,您可以将此查询用作虚拟表.例如,您可以执行此操作以获取用户43的朋友列表.

SELECT friend

FROM (

SELECT f1.user1_id AS user,

f1.user2_id AS friend

FROM friendships AS f1

JOIN friendships f2

ON (f1.user1_id = f2.user2_id AND f1.user2_id = f2.user1_id)

) AS friends

WHERE user = 43

您可能希望将您的朋友查询设置为视图,就像这样.

CREATE VIEW friends AS (

SELECT f1.user1_id AS user,

f1.user2_id AS friend

FROM friendships AS f1

JOIN friendships f2

ON (f1.user1_id = f2.user2_id AND f1.user2_id = f2.user1_id)

)

这样你就可以像这样缩写复杂的查询.

SELECT friend FROM friends WHERE user = 43;

您的多汁查询也很容易：

SELECT thread_id,owner_id,message,time

FROM threads AS th

JOIN (

SELECT f1.user1_id AS user,

f1.user2_id AS friend

FROM friendships AS f1

JOIN friendships f2

ON (f1.user1_id = f2.user2_id AND f1.user2_id = f2.user1_id)

) AS f ON th.owner_id = f.friend

WHERE f.user = 43

如果您使用该视图,则可以执行此操作.它意味着相同的东西,但更容易阅读.

SELECT thread_id,owner_id,message,time

FROM threads AS th

JOIN friends AS f ON th.owner_id = f.friend

WHERE f.user = 43

(注意：JOIN和INNER JOIN是同义词.)

看看怎么样？您可以封装您的朋友查询(作为视图或仅作为虚拟表内联)并使用其结果.当您指定诸如WHERE f.user = 43之类的内容时,优化器知道如何快速执行此操作.

如果友谊表中有很多行,您可能会发现一对复合索引(user1_id,user2_id)和(user2_id,user1_id)有助于这些查询的执行

(这不是递归查询,尽管有相反的评论.)

标签：mysql,database,join,inner-join,sql

来源： https://codeday.me/bug/20190624/1280151.html