当运行"SELECT INNER JOIN WHERE t1.column=t2.column"并且

Question

我有两个MySQL表：

产品

title    id    hidden   categories
Thing    10    N        12,14,
Stuff    23    N        12,
Object   41    Y        13,14

图像

filename     id    productid
aca8t.jpg    1     10
ev7ha.jpg    2     10
mscpk.jpg    3     10
asges.jpg    4     23
fcuhg.jpg    5     23
scvfe.jpg    6     41
vf6kl.jpg    7     41
fgszy.jpg    8     41

我使用SELECT语句构建了一个产品标题和图像列表，如下所示：

SELECT     t1.title,
           t1.id,
           t1.hidden,
           t1.categories,
           t2.image
FROM       products AS t1
INNER JOIN pimage   AS t2
WHERE      t1.categories LIKE '%$categoryId%'
AND        t1.id=t2.productid
AND        NOT t1.hidden='Y'
GROUP BY   id
ORDER BY   id ASC

运行此查询后，我得到了一个给定类别中的非隐藏产品列表，以及它们的ID和一个图像。然而，图像的选择似乎是随机的。有时是按字母顺序排列的，有时是最低ID，有时两者都不是。然而，当给定产品的文件名保持不变时，它总是相同的。

这在一个小型网站上使用，产品经理可以上传产品及其配件的照片。第一张照片应该用作缩略图，并且在类别页面上可见。然而，有时会选择随机配件的照片作为缩略图，产品经理必须重新上传图像，直到选择正确的图像。这个过程是繁重的。

如何修改SQL语句以选择第一张照片(images.id最低的文件名(？

Answer 1

尝试使用相关子查询而不是join/group by:

SELECT p.*,
       (SELECT i.image
        FROM pimage i
        WHERE p.id = i.productid 
        ORDER BY i.image ASC
        LIMIT 1
       ) as image
FROM  products p
WHERE p.categories LIKE '%$categoryId%' AND
      p.hidden <> 'Y'
ORDER BY p.id ASC

Answer 2

如果在另一个关系数据库(MySQL/MariaDB除外(上执行此语句，它将抛出一个错误，并显示一行消息"SELECT列表中的非聚合不包括在GROUP BY中"。

但是，GROUP BY的MySQL特定扩展允许在MySQL中执行此查询，但正如您所注意到的，SELECT列表中为非聚合返回的值是不确定的，MySQL将从某行返回一个值。

正常模式是使用MAX()或MIN()聚合函数来"控制"返回的值。

在您的情况下，返回最小的id值是可行的，但在同一行中获取其他值的问题更大。如果只需要返回几列，则可以在SELECT列表中使用相关的子查询。

另一种方法是使用内联视图和联接操作。

  SELECT t1.title
       , t1.id
       , t1.hidden
       , t1.categories
       , t2.image
    FROM products t1
    JOIN ( SELECT n.productid
                , MIN(n.id) AS min_id
             FROM pimage n
            GROUP BY n.productid
         ) m
      ON m.productid = t1.id
    JOIN pimage t2
      ON t2.id = m.min_id
   WHERE t1.categories LIKE '%$categoryId%'
     AND t1.hidden='Y'
   GROUP BY t1.id
   ORDER BY t1.id ASC

当您需要从具有"最小"id的行返回额外的列时，这种方法很有用。例如，您还需要在SELECT列表中包括：

       , t2.fee
       , t2.fi
       , t2.fo
       , t2.fum