如何用CASE表达式实现多区间非互斥累计计数？

现有一张AGE表，其中包含age列，取值范围为1到100。需要统计以下区间的累计记录数量：<=10、>10、>30、>60、>90。

我原本使用CASE表达式做互斥分组统计，代码如下：

select age_bracket, count(*) as nbr  
from 
(
select 
case when age > 90 then 'Nbr >90 days'
when age > 60 then 'Nbr >60 days'
when age > 30 then 'Nbr >30 days'
when age > 10 then 'Nbr >10 days'
when age <=10 then 'Nbr <=10 days'
else 'n/a' end as age_bracket    
from [AGE]
)z
group by age_bracket

得到的互斥分组统计结果为：

Age Bracket     Nbr
Nbr <=10 days   10
Nbr >10 days    20
Nbr >30 days    30
Nbr >60 days    30
Nbr >90 days    10

但我需要的是各条件对应的所有符合记录的累计数量，期望结果如下：

Age Bracket     Nbr
Nbr <=10 days   10
Nbr >10 days    90
Nbr >30 days    70
Nbr >60 days    40
Nbr >90 days    10

请问是否可以通过CASE表达式实现该需求？如果可以，应如何编写表达式以得到上述结果？

可以用CASE表达式实现，核心是放弃互斥分支的写法，改为对每个区间单独判断并聚合。以下是两种可行方案：

方案一：通用写法（兼容所有SQL数据库） #

SELECT 'Nbr <=10 days' AS age_bracket, SUM(CASE WHEN age <=10 THEN 1 ELSE 0 END) AS nbr
FROM [AGE]
UNION ALL
SELECT 'Nbr >10 days' AS age_bracket, SUM(CASE WHEN age >10 THEN 1 ELSE 0 END) AS nbr
FROM [AGE]
UNION ALL
SELECT 'Nbr >30 days' AS age_bracket, SUM(CASE WHEN age >30 THEN 1 ELSE 0 END) AS nbr
FROM [AGE]
UNION ALL
SELECT 'Nbr >60 days' AS age_bracket, SUM(CASE WHEN age >60 THEN 1 ELSE 0 END) AS nbr
FROM [AGE]
UNION ALL
SELECT 'Nbr >90 days' AS age_bracket, SUM(CASE WHEN age >90 THEN 1 ELSE 0 END) AS nbr
FROM [AGE]

逻辑说明： #

• 每个SELECT语句单独针对一个区间，用CASE判断当前记录是否符合区间条件：符合则返回1，否则返回0
• 用SUM()聚合这些1和0，得到该区间的累计符合记录数
• 最后用UNION ALL将所有区间的统计结果合并，保证结果顺序与需求一致

方案二：高效写法（仅扫描一次表，支持窗口函数的数据库适用） #

如果你的数据库支持CTE和窗口函数（如MySQL 8.0+、SQL Server、PostgreSQL等），可以用以下写法减少表扫描次数：

WITH total_counts AS (
    SELECT
        SUM(CASE WHEN age <=10 THEN 1 ELSE 0 END) AS cnt_le10,
        SUM(CASE WHEN age >10 THEN 1 ELSE 0 END) AS cnt_gt10,
        SUM(CASE WHEN age >30 THEN 1 ELSE 0 END) AS cnt_gt30,
        SUM(CASE WHEN age >60 THEN 1 ELSE 0 END) AS cnt_gt60,
        SUM(CASE WHEN age >90 THEN 1 ELSE 0 END) AS cnt_gt90
    FROM [AGE]
)
SELECT 'Nbr <=10 days' AS age_bracket, cnt_le10 AS nbr FROM total_counts
UNION ALL
SELECT 'Nbr >10 days' AS age_bracket, cnt_gt10 AS nbr FROM total_counts
UNION ALL
SELECT 'Nbr >30 days' AS age_bracket, cnt_gt30 AS nbr FROM total_counts
UNION ALL
SELECT 'Nbr >60 days' AS age_bracket, cnt_gt60 AS nbr FROM total_counts
UNION ALL
SELECT 'Nbr >90 days' AS age_bracket, cnt_gt90 AS nbr FROM total_counts

这种写法只需要扫描一次AGE表，性能更优，适合数据量较大的场景。

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