带WHERE条件的UNION ALL异常：存储过程返回重复行问题排查

问题背景 #

我有一个接收表类型参数的存储过程CheckPolicy，核心逻辑是检查Payment表中是否存在对应账户：存在返回VALID，不存在返回INVALID。但由于Payment表中PolicyNumber存在重复值，当前存储过程会返回多条重复的VALID记录，不符合每个账户仅返回一行的预期。我尝试将最终查询改为SELECT DISTINCT CorrectAccount, PolicyStatus FROM #TempReferencIds，请问该方案是否正确？

相关代码与数据 #

存储过程与表类型定义

CREATE PROCEDURE [dbo].[CheckPolicy]    
    @SearchAccount AS dbo.PayerSwapType READONLY    
AS  
BEGIN  
    SET NOCOUNT ON;  
  
    CREATE TABLE #TempReferencIds
    (
        CorrectAccount nvarchar(20),
        PolicyStatus nvarchar(10),
    )

    INSERT INTO #TempReferencIds (CorrectAccount, PolicyStatus)
        SELECT t.CorrectAccount, 'VALID' 
        FROM [dbo].Payment a 
        JOIN @SearchAccount t ON a.PolicyNumber = t.CorrectAccount

        UNION ALL
    
        SELECT t.CorrectAccount, 'INVALID' 
        FROM [dbo].Payment a 
        RIGHT JOIN @SearchAccount t ON a.PolicyNumber = t.CorrectAccount
        WHERE a.PolicyNumber IS NULL

    SELECT CorrectAccount, PolicyStatus 
    FROM #TempReferencIds
END

CREATE TYPE dbo.PayerSwapType AS TABLE
(
     CorrectAccount VARCHAR(20) NOT NULL
);

Payment表数据（PolicyNumber可重复）

PolicyNumber           AccountNumber
--------------------------------------------
7003741019             ljksdahfkjA9992
7003723876             101100QTGRWI1499
7003723876             201100QTGRWI1499
7003741001             63e190SSSIUU0075
7003741001             60ds190SSSIUU0075
7003740987             601s90SSSIUU0075
7003740987             601s90SSSIUU0075
7003740987             601h90SSSIUU0075

当前返回结果

CorrectAccount  PolicyStatus
---------------------------
7003741019      VALID
7003723876      VALID
7003723876      VALID
7003741001      VALID
7003741001      VALID
7003740987      VALID
7003740987      VALID
7003740987      VALID
213786777       INVALID

预期返回结果

CorrectAccount   PolicyStatus
-----------------------------
7003741019       VALID
7003723876       VALID
7003741001       VALID
7003740987       VALID
213786777        INVALID

输入参数

7003741019
7003723876
7003741001
7003740987
213786777

修改后的查询语句

select distinct CorrectAccount, PolicyStatus
from #TempReferencIds  

解决方案分析 #

原修改方案的正确性 #

你提出的SELECT DISTINCT方案是正确的。DISTINCT关键字会对结果集去重，确保每个CorrectAccount只返回一行对应的PolicyStatus，完全符合你的预期结果。

更高效的优化方案 #

虽然DISTINCT能解决问题，但原存储过程先插入大量重复数据到临时表再去重，存在不必要的IO开销。可以直接通过一次查询完成判断，避免生成重复数据，性能更优：

CREATE PROCEDURE [dbo].[CheckPolicy]    
    @SearchAccount AS dbo.PayerSwapType READONLY    
AS  
BEGIN  
    SET NOCOUNT ON;  

    SELECT 
        t.CorrectAccount,
        CASE WHEN EXISTS (SELECT 1 FROM [dbo].Payment a WHERE a.PolicyNumber = t.CorrectAccount)
             THEN 'VALID'
             ELSE 'INVALID'
        END AS PolicyStatus
    FROM @SearchAccount t
END

或者使用LEFT JOIN结合DISTINCT（不过EXISTS通常性能更优）：

CREATE PROCEDURE [dbo].[CheckPolicy]    
    @SearchAccount AS dbo.PayerSwapType READONLY    
AS  
BEGIN  
    SET NOCOUNT ON;  

    SELECT DISTINCT
        t.CorrectAccount,
        CASE WHEN a.PolicyNumber IS NOT NULL THEN 'VALID' ELSE 'INVALID' END AS PolicyStatus
    FROM @SearchAccount t
    LEFT JOIN [dbo].Payment a ON a.PolicyNumber = t.CorrectAccount
END

这两种方案都不需要临时表，直接从输入参数表和Payment表关联判断，一次性得到每个账户的状态，避免了先插入重复数据再去重的冗余步骤，执行效率更高。

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