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