如何为一对多执行内部联接，其中多对多只返回一个

Question

我有这个查询

SELECT  p.productId,
        p.productTitle,
        p.productPrice1,
        p.productPrice2,
        p.productPart,
        pi.productImageTitle,
        pi.productImageDescription,
        pi.productImageFile,
        pi.productImageOrder
FROM product AS p
LEFT JOIN productImage AS pi
    ON p.productId = pi.productId
ORDER BY p.productId ASC

SQL 摆弄测试数据

我想要的是所有产品，无论它们是否有图像，但如果有加入的图像，我只想得到一个。 目前对于产品 B，它将返回 4 行，因为有 4 个图像。

我确实尝试在INNER JOIN中用LIMIT 1进行子SELECT，但这只允许连接一个图像。 此外，正如您在数据中看到的那样，productImageOrder并不总是从一个开始，所以我不能只过滤INNER JOIN中的非 1

Answer 1

为产品添加 GROUP BY 应该可以做到这一点

SELECT  p.productId,
    p.productTitle,
    p.productPrice1,
    p.productPrice2,
    p.productPart,
    pi.productImageTitle,
    pi.productImageDescription,
    pi.productImageFile,
    pi.productImageOrder
FROM product AS p
LEFT JOIN productImage AS pi
ON p.productId = pi.productId
GROUP BY p.productId
ORDER BY p.productId ASC

Answer 2

使用

SELECT  p.productId,
        p.productTitle,
        p.productPrice1,
        p.productPrice2,
        p.productPart,
        pi.productImageTitle,
        pi.productImageDescription,
        pi.productImageFile,
        pi.productImageOrder
FROM product AS p
LEFT JOIN productImage AS pi
    ON p.productId = pi.productId
GROUP BY p.productId
ORDER BY p.productId ASC

本质上只是添加一个GROUP BY子句。

请注意，这是一个MySQLism-其他RDBMS将抛出"聚合无效使用"或类似错误。

Answer 3

如何使用选择来获取要加入的产品图像ID

SELECT  p.productId,
            p.productTitle,
            p.productPrice1,
            p.productPrice2,
            p.productPart,
            pi.productImageTitle,
            pi.productImageDescription,
            pi.productImageFile,
            pi.productImageOrder

    FROM product AS p
    LEFT JOIN productImage AS pi
        ON pi.productImageID = (select productImageId from productImage as pi2 where p.productId = pi2.productId order by productImageOrder Limit 1)
    ORDER BY p.productId ASC

SQL 小提琴

这种方法实际上可能适用于其他RDBMS，如果您没有将其他字段放在聚合函数中，例如MIN，MAX，SUM等，则无法使用GROUP BY。

Answer 4

如果要

避免外部聚合，可以使用连接：

select p.*, pi.*
from (select p.*,
             (select pi.productImageId
              from productImage pi
              where p.productId = pi.productId
             ) as productImageId
      from product p
     ) p join
     productImage pi
     on p.productImageId = pi.productImageId
order by p.productId;

这种方法的三个优点是：

1. 具体化子查询可能比执行聚合更快。
2. 您可以更好地控制拍摄的图像(在相关子查询中使用order by(。
3. 这是标准的SQL，不使用任何MySQL扩展。

您确实希望productImage(productId)和productImage(productImageId)上的索引以获得最佳性能。