草庐IT

mysql - INNER JOIN 表自身

coder 2023-10-10 原文

我有一张表，代表两个用户之间的友谊。每个条目都是单向的；友谊需要两个条目来表示。我想保持这种状态。

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;

为了进入更有趣的情况，我还需要能够查询以获取用户的 friend 创建的线程列表。我仍在适应使用联接所需的抽象思维，但我想出的解决方案是:

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

这似乎可行，但我正在一个非常空的数据库上对其进行测试。由于我对 join 的掌握还有些薄弱，恐怕在某些情况下这会返回不好的结果。所以我的问题是:

  1. 这两个查询的行为是否符合我的预期？
  2. 有没有更好的方法来解决这个问题？

最佳答案

让我们开始通过创建一个查询来回答这个问题，该查询给出所有(用户、 friend )对的列表。您要求一对 friend 是互惠关系，所以我们需要这样做。否则，friendships 表将提供这些对。

您的查询非常接近。此查询需要的是内部联接。

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

您可能希望将您的 friend 查询设置为一个 View ，就像这样。

 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

如果你使用 View ，你可以做到这一点。它的意思相同，但更容易阅读。

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 是同义词。)

看看进展如何？您可以封装您的 friends 查询(作为 View 或作为虚拟表内联)并使用其结果。当您指定诸如 WHERE f.user = 43 之类的内容时，优化器知道如何快速执行此操作。

如果您在friendships 表中有很多行，您可能会发现一对复合索引(user1_id, user2_id) 和(user2_id, user1_id) 帮助这些查询的性能

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

关于mysql - INNER JOIN 表自身，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/21663239/

mysqlINNERusercodeuser1_idsqldatabasejoininner-join

有关mysql - INNER JOIN 表自身的更多相关文章

  1. 使用canal同步MySQL数据到ES - 2

    文章目录一、概述简介原理模块二、配置Mysql使用版本环境要求1.操作系统2.mysql要求三、配置canal-server离线下载在线下载上传解压修改配置单机配置集群配置分库分表配置1.修改全局配置2.实例配置垂直分库水平分库3.修改group-instance.xml4.启动监听四、配置canal-adapter1修改启动配置2配置映射文件3启动ES数据同步查询所有订阅同步数据同步开关启动4.验证五、配置canal-admin一、概述简介canal是Alibaba旗下的一款开源项目，Java开发。基于数据库增量日志解析，提供增量数据订阅&消费。Git地址：https://github.co

  2. ruby-on-rails - 无法安装 mysql2 0.3.14 gem - 2

    我看到其他人也遇到过类似的问题，但没有一个解决方案对我有用。0.3.14gem与其他gem文件一起存在。我已经完全按照此处指示完成了所有操作:https://github.com/brianmario/mysql2.我仍然得到以下信息。我不知道为什么安装程序指示它找不到include目录，因为我已经检查过它存在。thread.h文件存在，但不在ruby​​目录中。相反，它在这里:C:\RailsInstaller\DevKit\lib\perl5\5.8\msys\CORE\我正在运行Windows7并尝试在Aptana3中构建我的Rails项目。我的Ruby是1.9.3。$gemin

  3. ruby - 如何使用 ruby​​ mysql2 执行事务 - 2

    我已经开始使用mysql2gem。我试图弄清楚一些基本的事情——其中之一是如何明确地执行事务(对于批处理操作，比如多个INSERT/UPDATE查询)。在旧的ruby-mysql中，这是我的方法:client=Mysql.real_connect(...)inserts=["INSERTINTO...","UPDATE..WHEREid=..",#etc]client.autocommit(false)inserts.eachdo|ins|beginclient.query(ins)rescue#handleerrorsorabortentirelyendendclient.commi

  4. ruby - 一种语言如何被自身解释(如 Rubinius)？ - 2

    我使用Ruby编程已经有一段时间了，现在只使用Ruby的标准MRI实现，但我一直对我经常听到的其他实现感到好奇。前几天我在读有关Rubinius的文章，这是一个用Ruby编写的Ruby解释器。我试着在不同的地方查找它，但我很难弄清楚这样的东西到底是如何工作的。我在编译器或语言编写方面从来没有太多经验，但我真的很想弄明白。一门语言究竟如何才能被自己解释？编译中是否有一个我不明白这有意义的基本步骤？有人可以像我是个白痴一样向我解释这个吗(因为无论如何这都不会太离谱) 最佳答案 它比你想象的要简单。Rubinius并非100%用Ruby编

  5. ruby-on-rails - 当我通过 rvm 使用 rails3 时，如何在 ubuntu 上安装 mysql2 gem？ - 2

    我正在尝试绕过rails配置这个极其复杂的迷宫。到目前为止，我设法在ubuntu上设置了rvm(出于某种原因，ruby在ubuntu存储库中已经过时了)。我设法建立了一个Rails项目。我希望我的测试项目使用mysql而不是mysqlite。当我尝试“rakedb:migrate”时，出现错误:“!!!缺少mysql2gem。将其添加到您的Gemfile:gem'mysql2'”当我尝试“geminstallmysql”时，出现错误，告诉我需要为安装命令提供参数。但是，参数列表很大，我不知道该选择哪些。如何通过在ubuntu上运行的rvm和mysql获取rails3？谢谢。

  6. Centos7-yum安装mysql-修改密码-无密码登录-安全配置 - 2

    目录1、yum安装mysql修改密码(1)在mysql里面修改(2)第二种方式，利用mysqladmin修改密码2、没有密码，登录mysql修改密码3、mysql的安全设置1、yum安装mysql在CentOS中默认安装有MariaDB（MySQL的一个分支），安装完成之后可以直接覆盖MariaDB。rpm-qa|grepmariadb查询是否安装了mariadbrpm-e--nodepsmariadb-libs-5.5.60-1.el7_5.x86_64卸载mariadwgethttp://dev.mysql.com/get/mysql57-community-release-el7-11.

  7. 驱动开发：内核无痕隐藏自身分析 - 2

    在笔者前面有一篇文章《驱动开发：断链隐藏驱动程序自身》通过摘除驱动的链表实现了断链隐藏自身的目的，但此方法恢复时会触发PG会蓝屏，偶然间在网上找到了一个作者介绍的一种方法，觉得有必要详细分析一下他是如何实现的进程隐藏的，总体来说作者的思路是最终寻找到MiProcessLoaderEntry的入口地址，该函数的作用是将驱动信息加入链表和移除链表，运用这个函数即可动态处理驱动的添加和移除问题。MiProcessLoaderEntry(pDriverObject->DriverSection,1)添加MiProcessLoaderEntry(pDriverObject->DriverSection,

  8. ruby - 安装 dm-mysql-adapter 时出错 - 2

    我是Ruby的新手。我安装了DataMapper并且正在尝试安装dm-mysql-adapter-1.0.2gem。但是当我尝试安装时，出现以下错误。我正在使用ubuntu操作系统。vinoth@vinoth-laptop:~/Downloads$geminstalldm-mysql-adapter-1.0.2----with-mysql-lib=/usr/lib/mysql----with-mysql-conf=/usr/bin/mysqlWARNING:Installingto~/.gemsince/home/vinoth/gemsand/home/vinoth/gems/bina

  9. Ruby:是否有关键字可以从自身内部调用方法(类似于 super)？ - 2

    我想知道:在Ruby中，有没有一种方法可以在不使用其名称的情况下从自身内部调用方法？如果该方法是通过某些元编程技术创建的，那么通过其名称调用它可能会难以阅读。即使对于通常定义的方法，如果您不确定它的好名字，或者如果它的名字很长，通过一些关键字(类似于super)从自身内部调用它可能会很方便。 最佳答案 您可以使用Kernel#__method__以Symbol形式返回当前方法的名称。与super不同，它不是关键字而是常规方法，因此您必须将它连同必需的参数一起传递给send方法才能调用该方法。这是__method__返回的内容:obj

  10. ruby-on-rails - gem 列表中的 mysql2 gem 但获取项目找不到 gem - 2

    我目前正在构建一个需要mysql2gem的RoR项目。我成功安装了gem。因为它出现在我的gem列表中。[root@vc2cmmka035538nsimple_cms]#gemlist***LOCALGEMS***actionmailer(3.2.3)actionpack(3.2.3)activemodel(3.2.3)activerecord(3.2.3)activeresource(3.2.3)activesupport(3.2.14,3.2.3)arel(3.0.2)bigdecimal(1.1.0)builder(3.2.2,3.0.0)bundler(1.1.5)c2c_li

随机推荐