如何在SQL Server中按数值区间统计匹配行数？

原始数据 #

需求 #

按以下三个区间统计匹配行数，输出指定格式结果：

• Value < 4 → class 1
• 4 ≤ Value ≤ 8 → class 2
• Value > 8 → class 3

方法1：CASE表达式+GROUP BY（最直接） #

通过CASE将每行数据映射到对应的class分组，再统计每组的行数，是最常用的实现方式：

SELECT
    CASE
        WHEN Value < 4 THEN 1
        WHEN Value BETWEEN 4 AND 8 THEN 2
        WHEN Value > 8 THEN 3
    END AS class,
    COUNT(*) AS matches
FROM YourTableName -- 替换为你的实际表名
GROUP BY
    CASE
        WHEN Value < 4 THEN 1
        WHEN Value BETWEEN 4 AND 8 THEN 2
        WHEN Value > 8 THEN 3
    END
ORDER BY class;

输出结果：

方法2：构造基准表+左连接（确保全区间显示） #

如果需要保证即使某个区间没有匹配数据，也能输出对应的class行（matches为0），可以先定义区间基准表，再通过左连接完成统计：

WITH ClassRanges AS (
    SELECT 1 AS class, -999999 AS min_val, 3 AS max_val UNION ALL
    SELECT 2 AS class, 4 AS min_val, 8 AS max_val UNION ALL
    SELECT 3 AS class, 9 AS min_val, 999999 AS max_val
)
SELECT
    cr.class,
    COUNT(t.id) AS matches
FROM ClassRanges cr
LEFT JOIN YourTableName t 
    ON t.Value > cr.min_val AND t.Value <= cr.max_val
GROUP BY cr.class
ORDER BY cr.class;

优势：当某个区间无匹配数据时，仍会输出该行（例如如果没有Value>8的数据，class3的matches会显示0）。

方法3：UNION ALL拼接统计结果 #

分别统计每个区间的匹配数，再用UNION ALL拼接成最终结果，逻辑直观，适合单独调试每个区间的统计规则：

SELECT
    1 AS class,
    COUNT(CASE WHEN Value < 4 THEN 1 END) AS matches
FROM YourTableName
UNION ALL
SELECT
    2 AS class,
    COUNT(CASE WHEN Value BETWEEN 4 AND 8 THEN 1 END) AS matches
FROM YourTableName
UNION ALL
SELECT
    3 AS class,
    COUNT(CASE WHEN Value > 8 THEN 1 END) AS matches
FROM YourTableName;

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