多对多JOIN中按Table2记录数匹配更新Table1状态的SQL实现

需求 #

需要编写SQL判断特定客户固定金额的发票是否已生成，并更新Table1的Status字段。当前问题是Table1中同一客户同一金额的所有记录都会被标记为已生成（'I'）；期望实现：若Table2中某客户某金额有N条记录，则仅将Table1中对应客户金额的N条匹配记录标记为'I'（任意N条均可），剩余记录标记为'NI'。

数据示例 #

Table1 #

Table2 #

当前查询与问题 #

当前使用的查询语句：

select table1.serie, table1.customerid, table1.total,case when count(table2.customerid)/nullif(count(table1.customerid),0)>0 then 'I' else 'NI' end as Status 
from table1 
left join table2 
on table1.customerid=table2.customerid and table1.total=table2.total
GROUP BY table1.customerid, table1.total, table1.serie

当前输出 #

问题：只要Table2存在对应客户和金额的记录，Table1中所有匹配的行都会被标记为'I'，无法控制标记的数量。

期望输出 #

解决方案 #

核心思路是给两个表中同一客户、同一金额的记录分别添加行号，仅关联行号相同的记录，从而限制标记的数量。

1. 查询获取期望结果 #

WITH t1_ranked AS (
    SELECT 
        CustomerId,
        Total,
        serie,
        -- 按客户和金额分组，给Table1的记录编号
        ROW_NUMBER() OVER (PARTITION BY CustomerId, Total ORDER BY serie) AS rn
    FROM Table1
),
t2_counted AS (
    SELECT 
        Customerid,
        Total,
        -- 按客户和金额分组，给Table2的记录编号
        ROW_NUMBER() OVER (PARTITION BY Customerid, Total ORDER BY doc#) AS rn
    FROM Table2
)
SELECT 
    t1.CustomerId,
    t1.Total,
    CASE WHEN t2.rn IS NOT NULL THEN 'I' ELSE 'NI' END AS Status,
    t1.serie
FROM t1_ranked t1
LEFT JOIN t2_counted t2 
    ON t1.CustomerId = t2.Customerid 
    AND t1.Total = t2.Total 
    AND t1.rn = t2.rn
ORDER BY t1.CustomerId, t1.Total, t1.serie;

2. 直接更新Table1的Status字段 #

WITH t1_ranked AS (
    SELECT 
        CustomerId,
        Total,
        serie,
        Status,
        ROW_NUMBER() OVER (PARTITION BY CustomerId, Total ORDER BY serie) AS rn
    FROM Table1
),
t2_counted AS (
    SELECT 
        Customerid,
        Total,
        ROW_NUMBER() OVER (PARTITION BY Customerid, Total ORDER BY doc#) AS rn
    FROM Table2
)
UPDATE t1_ranked
SET Status = CASE WHEN t2.rn IS NOT NULL THEN 'I' ELSE 'NI' END
FROM t1_ranked t1
LEFT JOIN t2_counted t2 
    ON t1.CustomerId = t2.Customerid 
    AND t1.Total = t2.Total 
    AND t1.rn = t2.rn;

说明：ORDER BY子句可以根据实际需求调整（比如按创建时间、主键等），只要保证同一分组内的记录有稳定的排序即可。

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