将联接表中的结果限制为一行 [英] Limit results from joined table to one row
问题描述
这是一个简化的表结构:
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
);
一个产品可以有多张照片,每个产品的第一张产品照片(基于photo_order)是默认照片.
A product can have multiple photos, the first product photo for each product (based on the photo_order) is the default photo.
现在,我只需要产品详细信息页面上的所有照片,但是在列出多个产品的页面(例如产品目录页面)上,我只想显示默认照片.
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
我从下面的答案中找到了解决方案,谢谢大家!
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
这篇关于将联接表中的结果限制为一行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!