MySQL中按年龄区间统计保险总赔付金额的实现方法

可以直接在主查询中处理年龄区间，同时对payment字段求和，简化后的SQL如下：

SELECT
    CASE
        WHEN age < 11 THEN '0-10'
        WHEN age BETWEEN 11 AND 20 THEN '11-20'
        WHEN age BETWEEN 21 AND 30 THEN '21-30'
        WHEN age BETWEEN 31 AND 40 THEN '31-40'
        WHEN age BETWEEN 41 AND 50 THEN '41-50'
        WHEN age BETWEEN 51 AND 60 THEN '51-60'
    END AS age_range,
    SUM(payment) AS total_payment
FROM insurance
GROUP BY age_range
ORDER BY age_range;

修改说明 #

• 将payment字段加入查询，通过SUM(payment)计算每个年龄区间的总赔付金额，并用别名total_payment标注结果
• 用BETWEEN ... AND ...简化年龄区间的条件判断，让逻辑更清晰易读
• 移除冗余的子查询，直接从原表查询并分组，简化代码结构
• 若存在年龄超过60的记录，可在CASE分支最后添加ELSE '61+'，将这类记录归入单独区间

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