这是一个简化的表格结构：
TABLE products ( product_id INT (primary key,auto_increment),category_id INT,product_title VARCHAR,etc ); TABLE product_photos ( product_photo_id (primary key,product_id INT,photo_href VARCHAR,photo_order INT );
产品可以有多张照片,每个产品的第一张产品照片(基于photo_order)是默认照片.
现在,我只需要在产品详细信息页面上的所有照片,但在我列出多个产品的页面上,例如产品目录页面,我只想显示默认照片.
这显然不起作用,它将返回所有照片与每张照片重复的产品信息：
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
解决方法
使用：
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
