mysql - 带连接的复杂查询，如何检索 COUNT

Question

我有一个相当复杂的查询来从多个表中检索产品和属性。

SELECT SQL_CALC_FOUND_ROWS 
    p.*,
    product_shop.*,
    product_shop.id_category_default,
    pl.*,  
    pbn.*,
    MAX(image_shop.id_image) id_image,
    il.legend, 
    m.name manufacturer_name,
    0 as quantity
FROM ps_category_product cp
LEFT JOIN ps_category c ON (c.id_category = cp.id_category)
LEFT JOIN ps_product p ON p.id_product = cp.id_product
INNER JOIN ps_product_shop product_shop ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1)
LEFT JOIN ps_product_lang pl ON (pl.id_product = p.id_product AND pl.id_shop = 1  AND pl.id_lang = 7)

## ########### Added joins ###########
LEFT JOIN ps_product_base_names pbn ON pbn.id_product = p.id_product
INNER JOIN (
    SELECT base_name, MAX(id_product) AS Max_ID_product 
    FROM ps_product_base_names 
    WHERE id_product IN (568110,568129,568134,568135,568136,568137,568139,568140,568141,602911,612411,612413,612512,612513,612515,612612,612616,616213,616217)
    GROUP BY base_name) groupedpbn 
ON (pbn.base_name = groupedpbn.base_name AND pbn.id_product = groupedpbn.Max_ID_product)
## ########### End added ###########

LEFT JOIN ps_image i  ON (i.id_product = p.id_product) LEFT JOIN ps_image_shop image_shop ON (image_shop.id_image = i.id_image AND image_shop.id_shop = 1 AND image_shop.cover=1)
LEFT JOIN ps_image_lang il ON (image_shop.id_image = il.id_image AND il.id_lang = 7)
LEFT JOIN ps_manufacturer m ON (m.id_manufacturer = p.id_manufacturer)
WHERE product_shop.active = 1 AND product_shop.visibility IN ("both", "catalog")
AND c.nleft >= 3 AND c.nright <= 4
AND c.active = 1
AND p.id_product IN (568110,568129,568134,568135,568136,568137,568139,568140,568141,602911,612411,612413,612512,612513,612515,612612,612616,616213,616217)
GROUP BY product_shop.id_product
ORDER BY pl.name asc LIMIT 0,30

我添加了 2 个 JOIN(请参阅评论)来按基本名称检索产品，并且每个基本名称仅获得 1 个结果，以显示在主目录概述页面中。

这一切都工作正常，但现在我想获取按基本名称分组的产品数量。类似于:

COUNT(id_product) AS product_variations

因此，假设 id_product 568110、602911 和 612413 的产品都具有相同的基本名称，上述查询将返回 id_product 612413 作为结果。

但是如何获取结果列表中每个产品已聚合的 ID 数量(id_product 612413 的产品为 3)？

Answer 1

我在这里找到了解决方案:https://www.periscopedata.com/blog/use-subqueries-to-count-distinct-50x-faster.html

我的工作查询现在如下所示:

SELECT SQL_CALC_FOUND_ROWS 
    p.*,
    product_shop.*,
    product_shop.id_category_default,
    pl.*,  
    pbn.*,
    cnt.product_variations, ## This line was added for the COUNT specified in the JOIN (see below)
    MAX(image_shop.id_image) id_image,
    il.legend, 
    m.name manufacturer_name,
    0 as quantity
FROM ps_category_product cp
LEFT JOIN ps_category c ON (c.id_category = cp.id_category)
LEFT JOIN ps_product p ON p.id_product = cp.id_product
INNER JOIN ps_product_shop product_shop ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1)
LEFT JOIN ps_product_lang pl ON (pl.id_product = p.id_product AND pl.id_shop = 1  AND pl.id_lang = 7)

## ########### Added joins ###########
LEFT JOIN ps_product_base_names pbn ON pbn.id_product = p.id_product
INNER JOIN (
    SELECT base_name, MAX(id_product) AS Max_ID_product 
    FROM ps_product_base_names 
    WHERE id_product IN (568110,568129,568134,568135,568136,568137,568139,568140,568141,602911,612411,612413,612512,612513,612515,612612,612616,616213,616217)
    GROUP BY base_name) groupedpbn 
ON (pbn.base_name = groupedpbn.base_name AND pbn.id_product = groupedpbn.Max_ID_product)

## This JOIN was added to COUNT aggregated product IDs
LEFT JOIN ( 
    SELECT base_name, COUNT(id_product) AS product_variations 
    FROM ps_product_base_names 
    WHERE id_product IN (568110,568129,568134,568135,568136,568137,568139,568140,568141,602911,612411,612413,612512,612513,612515,612612,612616,616213,616217)
    GROUP BY base_name) cnt 
ON (pbn.base_name = cnt.base_name)
## ########### End added ###########

LEFT JOIN ps_image i  ON (i.id_product = p.id_product) LEFT JOIN ps_image_shop image_shop ON (image_shop.id_image = i.id_image AND image_shop.id_shop = 1 AND image_shop.cover=1)
LEFT JOIN ps_image_lang il ON (image_shop.id_image = il.id_image AND il.id_lang = 7)
LEFT JOIN ps_manufacturer m ON (m.id_manufacturer = p.id_manufacturer)
WHERE product_shop.active = 1 AND product_shop.visibility IN ("both", "catalog")
AND c.nleft >= 3 AND c.nright <= 4
AND c.active = 1
AND p.id_product IN (568110,568129,568134,568135,568136,568137,568139,568140,568141,602911,612411,612413,612512,612513,612515,612612,612616,616213,616217)
GROUP BY product_shop.id_product
ORDER BY pl.name asc LIMIT 0,30

说实话，它有效，并且在我的测试环境中似乎足够快，但我不知道这个解决方案是否有效。因此，仍然欢迎任何可能改进我的解决方案的评论。

谢谢，玛蒂