可以肯定已经问过这个问题,但是我不禁想知道为什么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返回一行。

10-05 20:32
查看更多