MySQL - 按价格分组 - 计数不同

Question

我有一个包含价格的product_outer表。我想按价格分组，然后数一数。下面的代码工作的类别“8”中的所有产品：

SELECT price_range, count(*) AS num 
FROM 
(SELECT CASE 

WHEN po.price >= 0 AND po.price < 10 THEN '0.00 - 9.99' 
WHEN po.price >= 10 AND po.price <= 20 THEN '10.00 - 19.99' 
WHEN po.price >= 20 AND po.price <= 30 THEN '20.00 - 29.99' 
WHEN po.price >= 30 AND po.price <= 40 THEN '30.00 - 39.99' 
WHEN po.price >= 40 AND po.price <= 50 THEN '40.00 - 49.99' 
ELSE '50+' 

END as price_range 

FROM product_category pc INNER JOIN product_outer po ON pc.product_outer_id = po.id 
WHERE pc.category_id = '8' AND po.active = '1' 
) AS price_summaries 
GROUP BY price_range 

它提供了以下结果：

price_range  num 
===================== 
10.00 - 19.99  1 
20.00 - 29.99  1 
30.00 - 39.99  1 

我需要进一步采取这一步骤。每个product_outer可以有多个product_inner记录。我可能需要包括在WHERE子句中product_inner数据，但是当我介绍该表中，计数变为失控：

SELECT price_range, count(*) AS num 
FROM 
(SELECT CASE 

WHEN po.price >= 0 AND po.price < 10 THEN '0.00 - 9.99' 
WHEN po.price >= 10 AND po.price <= 20 THEN '10.00 - 19.99' 
WHEN po.price >= 20 AND po.price <= 30 THEN '20.00 - 29.99' 
WHEN po.price >= 30 AND po.price <= 40 THEN '30.00 - 39.99' 
WHEN po.price >= 40 AND po.price <= 50 THEN '40.00 - 49.99' 
ELSE '50+' 

END as price_range 

FROM product_category pc INNER JOIN product_outer po ON pc.product_outer_id = po.id INNER JOIN product_inner pi ON pi.product_outer_id = po.id INNER JOIN product_attribute pa ON pa.product_inner_id = pi.id 
WHERE pc.category_id = '8' AND po.active = '1' 
) AS price_summaries 
GROUP BY price_range 

（注FROM线不同）

的输出如下：

price_range  num 
===================== 
10.00 - 19.99  10 
20.00 - 29.99  6 
30.00 - 39.99  6 

我只想计算不同的po.ID ...我该怎么做？

Answer 1

试试这个：

SELECT price_range, count(distinct id) AS num 
FROM 
(SELECT po.id, CASE 

WHEN po.price >= 0 AND po.price < 10 THEN '0.00 - 9.99' 
WHEN po.price >= 10 AND po.price <= 20 THEN '10.00 - 19.99' 
WHEN po.price >= 20 AND po.price <= 30 THEN '20.00 - 29.99' 
WHEN po.price >= 30 AND po.price <= 40 THEN '30.00 - 39.99' 
WHEN po.price >= 40 AND po.price <= 50 THEN '40.00 - 49.99' 
ELSE '50+' 

END as price_range 

FROM product_category pc INNER JOIN product_outer po ON pc.product_outer_id = po.id INNER JOIN product_inner pi ON pi.product_outer_id = po.id INNER JOIN product_attribute pa ON pa.product_inner_id = pi.id 
WHERE pc.category_id = '8' AND po.active = '1' 
) AS price_summaries 
GROUP BY price_range