MySQL实现评论表按分数区间分组统计的方法

Got it, let's simplify those 5 separate queries into one clean SQL statement. The key here is to categorize each score into its interval using a CASE expression (or a predefined list of intervals for full coverage) and then group by that category to calculate your averages and counts.

Option 1: Basic Grouping (Returns only intervals with records) #

This uses a CASE statement to map each score to its interval, then groups by that interval. It's straightforward but won't show intervals that have zero matching records:

SELECT
    CASE
        WHEN score >= 0 AND score < 1 THEN '0-1'
        WHEN score >= 1 AND score < 2 THEN '1-2'
        WHEN score >= 2 AND score < 3 THEN '2-3'
        WHEN score >= 3 AND score < 4 THEN '3-4'
        WHEN score >= 4 AND score <= 5 THEN '4-5'
    END AS score_interval,
    IFNULL(AVG(score), 0) AS avg_score,
    COUNT(*) AS record_count
FROM reviews
WHERE status = 1
GROUP BY score_interval
ORDER BY
    CASE score_interval
        WHEN '0-1' THEN 1
        WHEN '1-2' THEN 2
        WHEN '2-3' THEN 3
        WHEN '3-4' THEN 4
        WHEN '4-5' THEN 5
    END;

Option 2: Full Interval Coverage (Includes empty intervals) #

If you need to see all 5 intervals even when there are no matching records (matching your original 5 queries' behavior, which return 0 for avg and count when no rows exist), use a CTE to define the intervals explicitly and left join with your reviews table:

WITH score_intervals AS (
    SELECT '0-1' AS interval_name, 0.0 AS lower, 1.0 AS upper, 1 AS sort_key
    UNION ALL
    SELECT '1-2', 1.0, 2.0, 2
    UNION ALL
    SELECT '2-3', 2.0, 3.0, 3
    UNION ALL
    SELECT '3-4', 3.0, 4.0, 4
    UNION ALL
    SELECT '4-5', 4.0, 5.0, 5
)
SELECT
    si.interval_name,
    IFNULL(AVG(r.score), 0) AS avg_score,
    COUNT(r.id) AS record_count
FROM score_intervals si
LEFT JOIN reviews r
    ON r.status = 1
    AND (
        -- Handle upper bounds: < for all except 4-5 which includes 5
        (si.interval_name != '4-5' AND r.score >= si.lower AND r.score < si.upper)
        OR (si.interval_name = '4-5' AND r.score >= si.lower AND r.score <= si.upper)
    )
GROUP BY si.interval_name, si.sort_key
ORDER BY si.sort_key;

Quick Notes: #

• I fixed the boundary logic to avoid gaps (your original queries had a gap between 1 and 1.01 which is likely unintended). If you really need that gap, adjust the WHEN clauses or join conditions accordingly.
• IFNULL(AVG(score), 0) ensures that even if an interval has no records, the average shows as 0 instead of NULL.
• The sort_key in Option 2 keeps the intervals in the correct order in your results.

内容的提问来源于stack exchange，提问作者edica