电商后台需统计各价格区间商品数量,Excel 中常用 LOOKUP + 透视表,MySQL 单条 SQL 即可完成。
SELECT price_range, COUNT(*) AS number
FROM (
SELECT CASE
WHEN Price >= 0 AND Price <= 49.99 THEN '0-50'
WHEN Price >= 50 AND Price <= 99.99 THEN '50-100'
WHEN Price >= 100 AND Price <= 149.99 THEN '100-150'
WHEN Price >= 150 AND Price <= 199.99 THEN '150-200'
WHEN Price >= 200 AND Price <= 249.99 THEN '200-250'
ELSE '250+'
END AS price_range
FROM products
WHERE 1
) AS price_summaries
GROUP BY price_range
ORDER BY price_range;
+------------+--------+
| price_range| number |
+------------+--------+
| 0-50 | 1234 |
| 50-100 | 856 |
| 100-150 | 421 |
| 150-200 | 210 |
| 200-250 | 98 |
| 250+ | 45 |
+------------+--------+
SET @bins = '0,50,100,150,200,250';
SELECT
CONCAT(
COALESCE(NULLIF(SUBSTRING_INDEX(SUBSTRING_INDEX(@bins, ',', n), ',', -1), ''), '0'),
'-',
COALESCE(NULLIF(SUBSTRING_INDEX(SUBSTRING_INDEX(@bins, ',', n+1), ',', -1), ''), '+')
) AS price_range,
COUNT(*) AS number
FROM products
JOIN (SELECT 1 n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) nums
ON Price >= SUBSTRING_INDEX(SUBSTRING_INDEX(@bins, ',', n), ',', -1)
AND Price < COALESCE(NULLIF(SUBSTRING_INDEX(SUBSTRING_INDEX(@bins, ',', n+1), ',', -1), ''), 999999)
GROUP BY n
ORDER BY n;
-- MySQL 8.0.2+ 近似直方图
SELECT HISTOGRAM(Price, 10) FROM products;
-- 返回 JSON 格式桶分布,适合快速概览
Price 列加索引可加速范围过滤(若区间选择性高)。