SQL Server：获取与储蓄金额匹配的累计支付记录需求实现

我需要实现一个查询逻辑，针对每个账户（accountno）按照以下规则筛选记录：

• 如果saved_amount小于第一条ilno对应的total_amount，就取这条第一条ilno的记录；
• 反之，取满足saved_amount >= total_amount条件的最大ilno对应的记录。

测试数据与现有脚本 #

首先是我用来测试的临时表和现有查询脚本：

Declare @table1 TABLE (accountno varchar(max), saved_amount decimal)
INSERT INTO @table1 VALUES ('001',25), ('002',5)
Declare @table2 TABLE (accountno varchar(max), payamount decimal,ilno int)
INSERT INTO @table2 VALUES ('001',10,1), ('001',10,2), ('001',10,3), ('001',10,4), ('002',10,1), ('002',10,2);
WITH aa AS (
 SELECT a.* ,b.ilno ,b.payamount ,SUM(payamount) OVER (
 PARTITION BY a.accountno ORDER BY CAST(a.accountno AS INT) ,ilno
 ) AS total_amount
 FROM @table1 a LEFT JOIN @table2 b ON a.accountno = b.accountno
)
,bb AS (
 SELECT accountno ,MAX(ilno) AS ilno FROM aa WHERE saved_amount >= total_amount GROUP BY accountno
)
SELECT a.* FROM aa a INNER JOIN bb b on a.accountno =b.accountno AND a.ilno = b.ilno

当前结果与期望结果 #

现有脚本执行后只返回了一条记录：

但我期望的结果应该包含两条记录，其中accountno '002'要取第一条ilno的记录：

问题分析与修正方案 #

原来的脚本问题在于bb这个CTE只统计了存在满足saved_amount >= total_amount条件的账户，像'002'没有符合该条件的记录，所以被排除在外了。我们需要调整逻辑，同时处理两种情况：

修正后的脚本如下：

Declare @table1 TABLE (accountno varchar(max), saved_amount decimal)
INSERT INTO @table1 VALUES ('001',25), ('002',5)
Declare @table2 TABLE (accountno varchar(max), payamount decimal,ilno int)
INSERT INTO @table2 VALUES ('001',10,1), ('001',10,2), ('001',10,3), ('001',10,4), ('002',10,1), ('002',10,2);

WITH aa AS (
 SELECT 
     a.accountno,
     a.saved_amount,
     b.ilno,
     b.payamount,
     SUM(b.payamount) OVER (
         PARTITION BY a.accountno 
         ORDER BY b.ilno
     ) AS total_amount,
     -- 标记每个账户的第一条ilno记录
     ROW_NUMBER() OVER (PARTITION BY a.accountno ORDER BY b.ilno) AS rn
 FROM @table1 a 
 LEFT JOIN @table2 b ON a.accountno = b.accountno
),
bb AS (
 SELECT 
     accountno,
     -- 优先取满足条件的最大ilno，没有则取第一条ilno
     COALESCE(MAX(CASE WHEN saved_amount >= total_amount THEN ilno END), MIN(CASE WHEN rn = 1 THEN ilno END)) AS target_ilno
 FROM aa
 GROUP BY accountno
)
SELECT 
    aa.accountno,
    aa.saved_amount,
    aa.ilno,
    aa.payamount,
    aa.total_amount
FROM aa 
INNER JOIN bb ON aa.accountno = bb.accountno AND aa.ilno = bb.target_ilno;

修正说明 #

1. 在aa中新增了rn字段，用ROW_NUMBER()标记每个账户的第一条ilno记录；
2. 在bb中使用COALESCE函数：先尝试获取满足saved_amount >= total_amount的最大ilno，如果没有这个值（比如'002'），就取该账户第一条ilno的记录；
3. 最后通过关联aa和bb，筛选出每个账户对应的目标记录，这样就能得到期望的结果了。

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