mysql - LEFT JOIN 但使用 WHERE 标准时，行会丢失

Question

我有一个包含三个表的简单数据库。在数据库中，我有一个用于系统用户的表、一个用于竞赛应用程序的表，以及一个允许我跟踪哪些用户选择了要查看的应用程序的中间表。

表 1 = 用户(user_id、用户名、第一个、最后一个等...)
表 2 = 应用程序(application_id、company_name、url 等...)
表 3 = 选择(pick_id、user_id、application_id、picked)

我正在尝试编写一个 SQL 查询，该查询将显示已提交的所有应用程序，并且如果用户选择了任何单个应用程序，则会显示它已被“选择”(1=已选择，0=未选择) )。

所以对于 user_id = 1 我想看到:

列名称(application_id、company_name、picked)

1，富，1
2、酒吧、1
3、Alpha、空
4、Beta、Null

我尝试使用以下查询:

SELECT applications.application_id, applications.company_name, picks.picked
  FROM applications
  LEFT JOIN picks ON applications.application_id = picks.application_id
 ORDER BY applications.application_id ASC

返回的是:

1，富，1
1、富、1
2、酒吧，空
3、Alpha、空
4、测试版、空

我有第二个用户 (user_id = 2)，它也选择了应用程序 1 (“Foo”)，我知道它正在返回第二行。

然后我尝试通过在此处指定 user_id = 1 来限制范围:

SELECT applications.application_id、applications.company_name、picks.picked
来自应用程序
LEFT JOIN 选择 ON applications.application_id = picks.application_id
其中用户 ID = 1
ORDER BY applications.application_id ASC


现在我只得到:

1，富，1

关于如何获得我想要的东西有什么建议吗？同样，对于我希望看到的单个用户来说，理想情况是:

列名称(application_id、company_name、picked)

1，富，1
2、酒吧、1
3、Alpha、空
4、Beta、Null

Answer 1

您的数据库模式中有一个所谓的连接表。在你的例子中，它被称为picks。这允许您在用户和应用程序之间创建多对多关系。

要正确使用该连接表，您需要连接所有三个表。如果使用表别名(applications AS a 等)，这些查询会更容易编写

SELECT a.application_id, a.company_name, p.picked, u.user_id, u.username
  FROM applications AS a
  LEFT JOIN picks AS p ON a.application_id = p.application_id
  LEFT JOIN users AS u ON p.user_id = u.user_id
 ORDER BY a.application_id, u.user_id

这将为您提供所有应用程序以及创建这些应用程序的用户的列表。如果没有用户与应用程序相关，LEFT JOIN 操作将保留应用程序行，并且您将看到 picks 和 users< 中的列为 NULL 值 表。

现在，如果您向此查询添加 WHERE p.something = Something 或 u.something = Something 子句以尝试缩小表示范围，它会将 LEFT JOIN 子句转换为 INNER JOIN 子句的效果。也就是说，您不会保留其他表中没有匹配行的 applications 行。

如果您想在结果集中保留那些不匹配的行，请将条件放在第一个 ON 子句中，而不是 WHERE 子句中，如下所示。

SELECT a.application_id, a.company_name, p.picked, u.user_id, u.username
  FROM applications AS a
  LEFT JOIN picks AS p ON a.application_id = p.application_id AND p.user_id = 1
  LEFT JOIN users AS u ON p.user_id = u.user_id
 ORDER BY a.application_id, u.user_id

编辑 许多联接表(例如 picks 表)都是使用复合主键设置的，在您的示例中为 (application_id, user_id)。这确保了被连接的表之间的每个可能关系只有一行。在您的情况下，您可能有多个这样的行。

要仅使用这些行中的最新行(具有最高 pick_id 的行)需要更多工作。您需要一个子查询(虚拟表)来提取它，并检索 picked 的适当值，以便您的查询正常工作。所以现在事情变得有趣了。

SELECT MAX(pick_id) AS pick_id, 
       application_id, user_id
  FROM picks
 GROUP BY application_id, user_id

检索唯一的关系对。那很好。但接下来我们必须从这些行中获取 picked 列详细信息值。这需要另一个连接，使用 pick_id 的 MAX 值，如下所示

SELECT q.application_id, q.user_id, r.picked
  FROM (
        SELECT MAX(pick_id) AS pick_id,
               application_id, user_id
          FROM picks
         GROUP BY application_id, user_id
       ) AS q
  JOIN picks AS r ON q.pick_id = r.pick_id

因此，我们需要用这个小虚拟表(子查询)来代替原始查询中的 pick AS p 表。看起来像这样。

SELECT a.application_id, a.company_name, p.picked, u.user_id, u.username
  FROM applications AS a
  LEFT JOIN (
        SELECT q.application_id, q.user_id, r.picked
          FROM (
                SELECT MAX(pick_id) AS pick_id,
                       application_id, user_id
                  FROM picks
                 GROUP BY application_id, user_id
               ) AS q
          JOIN picks AS r ON q.pick_id = r.pick_id
            ) AS p ON a.application_id = p.application_id AND p.user_id = 1
  LEFT JOIN users AS u ON p.user_id = u.user_id
 ORDER BY a.application_id, u.user_id

一些开发人员更喜欢为像这里这样的子查询创建 VIEW 对象，而不是创建像这样的查询的俱乐部三明治。它不是出于愚蠢的心血来潮而被称为结构化查询语言，是吗？这些子查询有时可以是结构的元素。