SQL Server按账单编号区间汇总账单金额的问题求助

这问题我太熟悉了！要实现按医院分组，同时按账单编号的四个区间汇总金额，咱们用条件聚合（也就是在SUM里嵌套CASE WHEN）就能完美搞定，完全匹配你想要的结果格式。

核心SQL查询语句 #

假设你的账单表名为bill_table，执行下面的查询就能得到目标结果：

SELECT
  hospital AS Hospital,
  -- 汇总0-9区间的金额
  SUM(CASE WHEN Number BETWEEN 0 AND 9 THEN Amount ELSE 0 END) AS range1,
  -- 汇总10-19区间的金额
  SUM(CASE WHEN Number BETWEEN 10 AND 19 THEN Amount ELSE 0 END) AS range2,
  -- 汇总20-29区间的金额
  SUM(CASE WHEN Number BETWEEN 20 AND 29 THEN Amount ELSE 0 END) AS range3,
  -- 汇总30-39区间的金额
  SUM(CASE WHEN Number BETWEEN 30 AND 39 THEN Amount ELSE 0 END) AS range4,
  -- 汇总医院总金额
  SUM(Amount) AS Total
FROM
  bill_table
GROUP BY
  hospital
ORDER BY
  hospital;

逻辑说明 #

• 用GROUP BY hospital把数据按医院分组，这部分你已经熟悉了。
• 每个SUM(CASE ...)就是条件求和：判断当前账单的编号属于哪个区间，符合条件就把金额计入该区间的总和，不符合就加0（不影响总和）。
• 最后一行的SUM(Amount)直接计算每个医院的所有账单总金额，和前面四个区间的金额加起来是完全相等的，可以用来验证数据正确性。

简化写法（针对部分数据库） #

如果你的数据库支持IF函数（比如MySQL），还可以把CASE WHEN简化成更简洁的IF写法，效果完全一致：

SELECT
  hospital AS Hospital,
  SUM(IF(Number BETWEEN 0 AND 9, Amount, 0)) AS range1,
  SUM(IF(Number BETWEEN 10 AND 19, Amount, 0)) AS range2,
  SUM(IF(Number BETWEEN 20 AND 29, Amount, 0)) AS range3,
  SUM(IF(Number BETWEEN 30 AND 39, Amount, 0)) AS range4,
  SUM(Amount) AS Total
FROM
  bill_table
GROUP BY
  hospital
ORDER BY
  hospital;

把你的示例数据代入这个查询，就能得到你想要的结果表啦～

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