代码之家 › 专栏 › 技术社区 › Rob

将联接表的结果限制为一行

mysql sql

Rob · 技术社区 · 15 年前

下面是一个简化的表结构:

TABLE products (
 product_id INT (primary key, auto_increment),
 category_id INT,
 product_title VARCHAR,
 etc
);

TABLE product_photos (
 product_photo_id (primary key, auto_increment),
 product_id INT,
 photo_href VARCHAR,
 photo_order INT
);

一个产品可以有多张照片,每个产品的第一张产品照片(基于照片订单)是默认照片。

现在,我只需要产品详细信息页面上的所有照片,但在列出多个产品的页面上,例如产品目录页面,我只想显示默认照片。

所以我要做的是查询产品列表,包括每个产品的默认照片。

这显然不起作用,它将返回所有照片,并为每个照片复制产品信息:

SELECT p.*, ph.*
FROM products AS p
LEFT JOIN product_photos AS ph
ON p.product_id=ph.product_id
ORDER BY p.product_title ASC

我需要弄清楚如何做这样的事情,但我不知道语法(或者如果可能的话)

SELECT p.*, ph.*
FROM products AS p
LEFT JOIN product_photos AS ph
    ON p.product_id=ph.product_id  **ORDER BY ph.photo_order ASC LIMIT 1**
ORDER BY p.product_title ASC

编辑:我从下面的答案中找到了一个解决方案,谢谢大家!

SELECT p.*, ph.*
FROM products AS p
LEFT JOIN product_photos AS ph 
    ON p.product_id=ph.product_id
    AND ph.photo_order =
    (
        SELECT MIN(z.photo_order)
        FROM product_photos AS z
        WHERE z.product_id=p.product_id
    )
GROUP BY p.product_id
ORDER BY p.product_title ASC

4 回复 | 直到 15 年前

OMG Ponies 15 年前

用途:

SELECT p.*,
       pp.*
  FROM PRODUCTS p
  JOIN PRODUCT_PHOTOS pp ON pp.product_id = p.product_id
  JOIN (SELECT x.product_id,
               MIN(x.photo_order) AS default_photo
          FROM PRODUCT_PHOTOS x
      GROUP BY x.product_id) y ON y.product_id = pp.product_id
                              AND y.default_photo  = pp.photo_order

Patrick M 10 年前

SELECT p.*, ph.*
FROM products AS p
INNER JOIN product_photos AS ph
    ON p.product_id = ph.product_id
LEFT JOIN product_photos AS ph2
    ON p.product_id = ph2.product_id
    AND ph2.photo_order < ph.photo_order
WHERE ph2.photo_order IS NULL
ORDER BY p.product_title ASC

注意它如何两次连接到product_photos表。这个 WHERE ph2.photo_order IS NULL 除了最低的照片顺序外,其他的都会被扔掉。它不会保护您免受重复产品\u id/照片\订单组合的伤害,但是您可以添加 GROUP BY 如果是这样的话。

Kovge 12 年前

    SELECT p.*, ph.*
    FROM products AS p
    LEFT JOIN product_photos AS ph ON p.product_id=ph.product_id
    ORDER BY p.product_title ASC, ph.photo_order ASC
    GROUP BY p.product_id
    LIMIT 0,10