MySQL 将连接表的结果限制为一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2978726/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Limit results from joined table to one row
提问by Rob
Here is a simplified table structure:
这是一个简化的表结构：
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
);
A product can have multiple photos, the first product photo for each product (based on the photo_order) is the default photo.
一个产品可以有多张照片，每个产品的第一张产品照片（基于photo_order）是默认照片。
Now, I only need all of the photos on the product details page, but on pages where I am listing multiple products, for example a product directory page, I only want to display the default photo.
现在，我只需要产品详细信息页面上的所有照片，但在我列出多个产品的页面上，例如产品目录页面，我只想显示默认照片。
So what I am trying to do, is query a list of products including the default photo for each product.
所以我想要做的是查询产品列表，包括每个产品的默认照片。
This obviously doesn't work, it will return all photos with the product info duplicated for each photo:
这显然不起作用，它将返回所有照片，并为每张照片复制产品信息：
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
I need to figure out how to do something like this, but I don't know the syntax (or if it is possible)
我需要弄清楚如何做这样的事情，但我不知道语法（或者是否可能）
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
Edit: I figured out a solution with help from the answers below, thanks all!
编辑：我从下面的答案中找到了一个解决方案，谢谢大家！
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
采纳答案by OMG Ponies
Use:
用：
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
回答by Wrikken
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
Note the how it joins to the product_photos table twice. The WHERE ph2.photo_order IS NULLwill throw out all but the lowest photo order. It won't protect you against duplicate product_id / photo_orders combo though, you could add a GROUP BYon p.id if that's the case.
请注意它如何连接到 product_photos 表两次。该WHERE ph2.photo_order IS NULL会抛出所有，但最低的照片顺序。它不会保护您免受重复的 product_id / photo_orders 组合的影响，GROUP BY如果是这种情况，您可以在 p.id 上添加一个。
回答by Kovge
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
回答by Ignacio Vazquez-Abrams
SELECT ...
....
GROUP BY p.product_id
