php - MySQL JOINS : Select one row from child table is condition is met, 否则为 NULL

Question

我正在尝试连接两个表，但遇到了一些问题。这是主表，packs

这是第二张表，media

media 用于存储packs 的图像。每个包可以有 0 个或多个图像。当包中有图像时，其中一个图像会将 is_default 字段设置为 1 以指示要显示的主图像。

我想获得所有包和每个包的一张图片。如果没有图像，则只是一个简单的 NULL，否则获取 is_default 的图像。

这是我的查询。

SELECT 
    pack.*,
    ( SELECT media.src FROM packs pack LEFT JOIN packs_media media ON pack.id = media.pack_id WHERE media.is_default = 1 GROUP BY media.id LIMIT 1 ) AS image 
FROM packs pack 
LEFT JOIN packs_media ON media.pack_id = pack.id 
GROUP BY pack.id 
ORDER BY pack.id DESC

我一共有三包，只有一包有一些图片。查询返回 3 个结果。问题在于所有三个结果都具有相同的 image，而只有一个应该而另外两个的 image 字段应为 null/empty。

有什么办法只用一个查询就可以做到这一点吗？我想避免在循环中查询 media 表。

谢谢

Answer 1

我最近遇到了类似的问题，老实说，它有一个非常非常棒的解决方案。乍一看，一个明显的尝试是添加 WHERE条件 is_default = 1 .然而，如果我们这样做，在没有图像的地方，条件将不满足，这意味着它应该返回 NULL 的地方。 , 它只是跳过那一行。

但是，如果将此条件添加到 LEFT JOIN部分，就像我所做的那样，它就像一个魅力。这是因为如果 LEFT JOIN满足条件，它返回图像，如果不满足，它返回 NULL , 根据 LEFT JOIN 的定义.

所以结果应该是:

SELECT pack.*, media.src
FROM packs pack 
LEFT JOIN media
ON media.pack_id = pack.id
AND media.is_default = 1

而且我认为您不应该添加 GROUP BY，它只会把事情搞砸。

如果即使没有 is_default 也想解决问题...好吧，这是一个更难解决的问题，但我认为我有一个解决方案，它不会陷入长行的条件和子查询中。可悲的是，我觉得如果没有子查询就无法解决这个问题。我的想法是不选择 media.is_default 的项目。等于 1 , 而哪个具有最高的 media.is_default .如下:

SELECT pack.*, media.src
FROM packs pack 
LEFT JOIN media
ON media.pack_id = pack.id
AND media.id = 
   (SELECT m.id FROM media m
    WHERE m.pack_id = media.pack_id
    ORDER BY m.is_default DESC
    LIMIT 1
   )

说明:它是这样工作的:我们全部按 is_default 订购。按降序排列，并返回外部查询以仅使用当前的 pack_id (我希望它能这样工作。)如果有一个 1，它显然将是第一个项目，否则它将是一个随机项目，很可能是 ID 顺序中的第一个。你只需获取该元素并使用它。我无法保证它是否有效。尝试使用 IN而不是 =作为可能的错误修复。

但是，我不建议使用此解决方案。我宁愿改变以前的逻辑来避免这个问题(例如，当用户将媒体添加到空包时，该图片上的 is_default = 1)。