使用窗口函数移除特定空transactionid记录的SQL需求

测试数据 #

declare @t table(claimid int, name varchar(100), benname varchar(100),amount int, transactionid 
                 int)
insert into @t values( 123, 'John Smith', 'Tom Smith', 500, 1234),
              (123, 'John Smith', NULL, NULL, NULL),
              (322, 'Tom Hanson', 'John Hanson', 1200, 5454),
              (322, 'Tom Hanson', 'Bob Hanson', 1200, 5455),
              (444, 'John Evers', NULL, NULL, NULL)

select * from @t

需求规则 #

• 同一claimid有多条记录时，移除其中transactionid为空的记录
• 若某个claimid仅有一条transactionid为空的记录，则保留该记录

期望输出 #

claimid   name          benname   amount    transactionid
123       John Smith    Tom Smith 500       1234
322       Tom Hanson    John Hanson 1200     5454      
322       Tom Hanson    Bob Hanson 1200      5455
444       John Evers    NULL       NULL      NULL

解决方案 #

可以通过窗口函数统计分组内的记录特征，再针对性筛选：

方案一：统计非空交易ID数量 #

WITH cte AS (
    SELECT 
        *,
        -- 统计当前claimid下transactionid非空的记录总数
        COUNT(transactionid) OVER (PARTITION BY claimid) AS non_null_tx_count
    FROM @t
)
SELECT claimid, name, benname, amount, transactionid
FROM cte
-- 筛选逻辑：要么交易ID非空，要么该claimid下无有效交易记录
WHERE transactionid IS NOT NULL OR non_null_tx_count = 0;

方案二：对比总记录数与非空记录数 #

WITH cte AS (
    SELECT 
        *,
        COUNT(*) OVER (PARTITION BY claimid) AS total_count,
        COUNT(transactionid) OVER (PARTITION BY claimid) AS non_null_count
    FROM @t
)
SELECT claimid, name, benname, amount, transactionid
FROM cte
-- 筛选逻辑：要么交易ID非空，要么该claimid只有一条空交易ID记录
WHERE transactionid IS NOT NULL OR (total_count = 1 AND transactionid IS NULL);

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