MYSQL - 获取最低列值行，同时保留 WHERE 语句

Question

我想从 product_images 中获取 position 为 1 或最接近它 (ASC) 的所有 path。

我正在尝试抓取特定类别中每个产品的第一个产品图片。

这是我的查询:

SELECT pi.product_id, path, position from product_images pi
JOIN product_categories pc ON (pc.product_id = pi.product_id)
WHERE pc.category_id = 59 
GROUP BY pi.product_id
ORDER BY pi.position ASC

此方法不起作用的是，它并不总是抓取位置为 1 的图像，而是占据某些产品的位置 2。

我认为这是因为我在group之后order by位置(它已经采取了随机行)？

如何才能使其正确排序？

表结构:

产品类别

category_id | product_id

产品图片

id | product_id | path | position

Answer 1

为什么不起作用

您需要表 product_image 中的一些行，但 GROUP BY 生成的结果不包含表中的行。 GROUP BY 计算它返回的值。此外，您在问题中发布的查询是无效的 SQL，因为 GROUP BY 查询的 SELECT 子句只允许包含以下类型的表达式:

• 也出现在 GROUP BY 子句中的表达式；
• 使用 GROUP BY aggregate functions 的表达式;
• 在功能上依赖于 GROUP BY 子句中存在的列。

只有查询的 SELECT 子句中的 pi.product_id 列在这里是安全的。如果您GROUP BY pi.id，则允许使用其他两列，例如

在版本 5.7.5 之前，MySQL 曾经允许此类无效查询(而其他 RDBMS 则拒绝它们)。但是，与上述任何规则都不匹配的表达式返回的值是 indeterminate 。但你已经发现了(我引用你的话:“它已经采取了随机行”)。

解决方案

应该生成您需要的结果的查询(我没有测试它)如下所示:

SELECT pi.product_id, pi.path, pi.position
FROM product_categories pc
  INNER JOIN product_images pi         # product image
    ON pi.product_id = pc.product_id
  LEFT JOIN product_images bi          # better image...
    ON bi.product_id = pi.product_id   # ... of the same product
       AND bi.position < pi.position   # ... better because of `position`
WHERE pc.category_id = 59 
  AND bi.id IS NULL         # keep only when there isn't a match in "better image"

它是如何工作的

查询连接product_categories，因为它只需要根据表product_images(别名为pi)选择特定类别的产品图像。这是您的查询中已有的内容，我只是交换了表的顺序以使查询更易于阅读和理解。接下来，它再次加入 product_images(别名为 bi，来自“更好的图像”，在位置方面更好)。

连接将左表中的每一行(在本例中是 pc 和 pi 之间的连接)与右表中的所有行( >bi)。它将每个产品图像(来自 pc JOIN pi)与具有更好位置的同一产品的所有图像进行匹配。因为它是LEFT JOIN，所以即使右表中没有匹配的行，它也会将左表中的所有行放入结果集中。在本例中，将使用充满 NULL 值的行，而不是右表中的行。

接下来，WHERE子句仅过滤我们需要的类别中的产品，更重要的是，仅过滤从表中提取的部分包含NULL的行>bi。但这些图像在“更好图像”表中没有匹配项；即他们拥有最佳(最小)位置。

最后，SELECT 子句包含表 pi 中所需的列(它也可以包含 pc 中的列)。无论如何，表 bi 中到达结果集中的所有列都是 NULL。