MySQL 为每个产品返回 1 个图像 [英] MySQL returning 1 image for each product
本文介绍了MySQL 为每个产品返回 1 个图像的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
这与将连接表中的结果限制为一行非常相似,但我正在努力让它完全按照我的需要工作......
This is very similar to Limit results from joined table to one row, but I'm struggling to get it to work exactly as I need...
表结构非常相似:
CREATE TABLE tblProducts (
intProductID int(11) NULL AUTO_INCREMENT,
strProductName varchar(255) NULL,
PRIMARY KEY (intProductID)
);
CREATE TABLE tblProductImages (
intImageID int(11) NULL AUTO_INCREMENT,
intProductID int(11) NULL,
strImageName varchar(255) NULL,
intOrder int(11) NULL,
PRIMARY KEY (intImageID)
);
使用以下内容填充表格:
Populate the tables with:
INSERT INTO tblProducts (strProductName)
VALUES
('Product #1'), ('Product #2'), ('Product #3');
INSERT INTO tblProductImages (intProductID, strImageName, intOrder)
VALUES
(1, 'image_for_1.jpg', 1),
(2, '1st_image_for_2.jpg', 1),
(2, '2nd_image_for_2.jpg', 2);
我想做一个 SELECT 返回所有 3 个产品,并带有适当的图像.类似于:
I want to do a SELECT that returns all 3 products, with appropriate images. Similar to:
intProductID, strProductName, strImageName
1, Product #1, image_for_1.jpg
2, Product #2, 1st_image_for_2.jpg
3, Product #3, NULL
一个简单的解决方案是:
A simple solution would be:
SELECT intProductID, strProductName,
(SELECT strImageName
FROM tblProductImages
WHERE tblProductImages.intProductID = tblProducts.intProductID
ORDER BY intOrder
LIMIT 1)
FROM tblProducts
但是如果我想添加 &从 tblProductImages 返回另一个字段(例如 AltText)
But this fails if I want to add & return another field from the tblProductImages (AltText for example)
我在 http://sqlfiddle.com/#!2/设置了一个 Fiddle883c5/1
谢谢
推荐答案
这个可以用,但是很丑
select p.intProductId, p.strProductName, pi.strImageName
from tblProducts p
inner join tblProductImages pi on pi.intProductID = p.intProductId
inner JOIN (select min(intOrder) minOrder, intProductID
FROM tblProductImages
GROUP BY intProductID) s
on s.intProductID = p.intProductID and s.minOrder = pi.intOrder
union
select p.intProductId, p.strProductName, pi.strImageName
from tblProducts p
left join tblProductImages pi on pi.intProductID = p.intProductId
where pi.intProductID is null;
替代:
select p.intProductId, p.strProductName, pi.strImageName
from tblProducts p
left join tblProductImages pi on pi.intProductID = p.intProductId
where pi.intProductId is null or pi.IntOrder = (select min(intOrder)
from tblProductImages
where intProductId = pi.intProductId);
这篇关于MySQL 为每个产品返回 1 个图像的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文