可以肯定已经问过这个问题,但是我不禁想知道为什么INNER JOINS中的“ LIMIT 3”只为“ ON子句”中指定的avp.productID返回一个imgUrl! LIMIT在INNER JOIN中不起作用吗?请问有什么更好的方法可以用来实现这一目标?
$query = "SELECT
p.productID,
p.productDesc,
p.productQty,
p.productPr,
p.type,
p.gender,
p.date
From
products AS p
INNER JOIN(
Select
c.productID,
GROUP_CONCAT(DISTINCT c.availCol) AS color_list
FROM
availColors AS c GROUP BY c.productID) AS colors
ON
p.productID = colors.productID
INNER JOIN(
SELECT
s.productID,
GROUP_CONCAT(s.availSizes) AS size_list
FROM
availSizes AS s
GROUP BY
s.productID
) AS sizes
ON
p.productID = sizes.productID
INNER JOIN(
SELECT
avp.productID,
avp.productImg
FROM
availImg AS avp
ORDER BY
avp.productID
LIMIT 3) AS images
ON
images.productID = p.productID
WHERE
p.productID = ?
GROUP BY
p.productID";
最佳答案
您当前的查询有一个子查询,该查询返回3张图像,按产品ID排序。此子查询不会检查图像是否属于您要搜索的产品,因此可以轻松带回3张图像用于其他产品。这将导致主查询尝试根据产品ID对子查询进行内部联接,并且找不到匹配的记录。
由于您的其他2个子查询仅带回每个产品ID的一行(由于GROUP_CONCAT),并且您似乎只想一次检查一个产品ID,所以我认为您可以通过对images表(没有子查询),并将限制放在主查询上。
像这样:-
$query = "SELECT
p.productID,
p.productDesc,
p.productQty,
p.productPr,
p.type,
p.gender,
p.date
From products AS p
INNER JOIN
(
Select c.productID,
GROUP_CONCAT(DISTINCT c.availCol) AS color_list
FROM availColors AS c
GROUP BY c.productID
) AS colors
ON p.productID = colors.productID
INNER JOIN
(
SELECT s.productID,
GROUP_CONCAT(s.availSizes) AS size_list
FROM availSizes AS s
GROUP BY s.productID
) AS sizes
ON p.productID = sizes.productID
INNER JOIN availImg images
ON images.productID = p.productID
WHERE p.productID = ?
ORDER BY images.productID, images.productImg
LIMIT 3";
但是,如果您想带回多个产品ID,那么它将变得更加复杂。一种解决方案是取回具有每个产品生成的序列号的图像,然后在联接的ON子句中检查它是产品的前三个图像之一:
$query = "SELECT
p.productID,
p.productDesc,
p.productQty,
p.productPr,
p.type,
p.gender,
p.date
From products AS p
INNER JOIN
(
Select c.productID,
GROUP_CONCAT(DISTINCT c.availCol) AS color_list
FROM availColors AS c
GROUP BY c.productID
) AS colors
ON p.productID = colors.productID
INNER JOIN
(
SELECT s.productID,
GROUP_CONCAT(s.availSizes) AS size_list
FROM availSizes AS s
GROUP BY s.productID
) AS sizes
ON p.productID = sizes.productID
INNER JOIN
(
SELECT avp.productID,
avp.productImg,
@seq:=IF(@prev_productID = avp.productID, @seq + 1, 1) AS seq,
@prev_productID := avp.productID
FROM availImg AS avp
CROSS JOIN (SELECT @prev_productID:=0, @seq:=0) sub1
ORDER BY avp.productID
) AS images
ON images.productID = p.productID
AND images.seq <= 3
WHERE p.productID = ?";
另一种可能的方法是使用GROUP_CONCAT来获取每个产品的图像ID的逗号分隔列表,使用SUBSTRING_INDEX来获取其中的前三个图像,然后使用FIND_IN_SET将其与图像表连接以获取其余图像细节:-
$query = "SELECT
p.productID,
p.productDesc,
p.productQty,
p.productPr,
p.type,
p.gender,
p.date
From products AS p
INNER JOIN
(
Select c.productID,
GROUP_CONCAT(DISTINCT c.availCol) AS color_list
FROM availColors AS c
GROUP BY c.productID
) AS colors
ON p.productID = colors.productID
INNER JOIN
(
SELECT s.productID,
GROUP_CONCAT(s.availSizes) AS size_list
FROM availSizes AS s
GROUP BY s.productID
) AS sizes
ON p.productID = sizes.productID
INNER JOIN
(
SELECT avp.productID,
SUBSTRING_INDEX(GROUP_CONCAT(avp.productImg), ',', 2), AS three_images_per_product
FROM availImg AS avp
GROUP BY avp.productID
) AS images
ON images.productID = p.productID
INNER JOIN availImg
ON availImg.productID = images.productID
AND FIND_IN_SET(availImg.productImg, images.three_images_per_product)
WHERE p.productID = ?";
请注意,您的原始查询实际上在SELECT子句中没有任何图像详细信息,并且您已经执行了
GROUP BY p.productID
,这意味着即使找到3张图像,该查询无论如何也只会针对每个产品ID返回一行。