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

问题描述：

我有一个相当复杂的查询来检索几个表中的产品&属性。带连接的复杂查询，如何检索COUNT

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连接（见注释）通过他们的基地名称检索产品，并获得每名基地仅1的结果，在主目录概述页面显示。

这一切工作正常，但现在我想获得已分组的每个基地名称的产品数量。喜欢的东西：

COUNT(id_product) AS product_variations

让我们假设产品id_product 568110，602911 & 612413都具有相同的基本名称，上面的查询将返回id_product 612413作为结果。

但是，如何获得结果列表中每个产品已汇总的ID的编号（3为id_product 612413的产品）？

答

我在这里找到了解决方案：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

说实话，它的工作原理，它在我的测试环境似乎不够快，但还没如果这个解决方案是有效的或者没有的话，我们就会得到线索所以任何意见可能改善我的解决方案仍然受欢迎。

谢谢，玛蒂

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

相关推荐