如何在SQL UNION查询中区分不同来源列的错误数据

这个问题其实很好处理——你只需要给每个子查询额外添加一列来标记错误的来源字段，这样就能在结果里清晰看到每个错误到底来自哪一列了。

修改后的SQL代码 #

SELECT DISTINCT 
    '[1001account]' AS SourceColumn,  -- 标记当前错误的来源列
    [1001account] AS ErrorValue       -- 统一错误值的列名
FROM [Cris_Ocean_tmp].[dbo].[NiiStressTest2018_v2_PUBLISHED_201812_CC_0326] 
WHERE 
    StresstestaccountEnabled LIKE '%Yes%' 
    AND BalancesheetAmount <> 0 
    AND (PATINDEX('%[^0-9]%', [1001account]) > 0 OR [1001account] IS NULL)  -- 加括号修正逻辑优先级
UNION ALL  -- 用UNION ALL避免不必要的去重，需全局去重可换回UNION
SELECT DISTINCT 
    '[5028account]' AS SourceColumn,
    [5028account] AS ErrorValue
FROM [Cris_Ocean_tmp].[dbo].[NiiStressTest2018_v2_PUBLISHED_201812_CC_0326] 
WHERE 
    StresstestaccountEnabled LIKE '%Yes%' 
    AND BalancesheetAmount <> 0 
    AND (PATINDEX('%[^0-9]%', [5028account]) > 0 OR [5028account] IS NULL)
UNION ALL
SELECT DISTINCT 
    '[BalanceSheetType]' AS SourceColumn,
    [BalanceSheetType] AS ErrorValue
FROM [Cris_Ocean_tmp].[dbo].[NiiStressTest2018_v2_PUBLISHED_201812_CC_0326] 
WHERE 
    StressTestAccountEnabled LIKE '%Yes%' 
    AND BalanceSheetAmount <> 0 
    AND [BalanceSheetType] NOT LIKE '%Assets%' 
    AND BalanceSheetType NOT LIKE '%Liabilities%'
UNION ALL
SELECT DISTINCT 
    'CorepRRR' AS SourceColumn,
    CorepRRR AS ErrorValue
FROM [Cris_Ocean_tmp].[dbo].[NiiStressTest2018_v2_PUBLISHED_201812_CC_0326] 
WHERE 
    StressTestAccountEnabled LIKE '%Yes%' 
    AND BalanceSheetAmount<>0 
    AND CorepRRR NOT LIKE '%D%' 
    AND CorepRRR NOT LIKE '%R%' 
    AND CorepRRR NOT LIKE '%P%' 
    AND CorepRRR NOT LIKE '%Not Applicable%'
UNION ALL
SELECT 
    'CoreGroupId' AS SourceColumn,
    [CoreGroupId] AS ErrorValue
FROM [Cris_Ocean_tmp].[dbo].[NiiStressTest2018_v2_PUBLISHED_201812_CC_0326] 
WHERE 
    StressTestAccountEnabled LIKE '%Yes%' 
    AND BalanceSheetAmount<>0 
    AND [CoreGroupId] NOT IN (2, 3, 4, 5, 6, 7, 8, 9, 11 ,14, 15, 99)
UNION ALL
SELECT 
    'CoreGroupDescription' AS SourceColumn,
    CoreGroupDescription AS ErrorValue
FROM [Cris_Ocean_tmp].[dbo].[NiiStressTest2018_v2_PUBLISHED_201812_CC_0326] 
WHERE 
    StressTestAccountEnabled LIKE '%Yes%' 
    AND BalanceSheetAmount<>0 
    AND [CoreGroupDescription] NOT IN ( 'DLL', 'ABB_Assets', 'ABB_Liab', 'LRDW', 'OBV', 'VRN', 'Force', 'ROB', 'HedgeAccounting', 'LRDW_US', 'CorepDefaultsCorrections', 'Corrections' )
UNION ALL
SELECT DISTINCT 
    '[Country]' AS SourceColumn,
    [Country] AS ErrorValue
FROM [Cris_Ocean_tmp].[dbo].[NiiStressTest2018_v2_PUBLISHED_201812_CC_0326] 
WHERE 
    StressTestAccountEnabled LIKE 'Yes' 
    AND BalanceSheetAmount <> 0 
    AND Country NOT LIKE '%[^a-z]%' 
    AND (LEN(Country)>2 OR LEN(Country)<2)
UNION ALL
SELECT DISTINCT 
    'CountryOfRisk' AS SourceColumn,
    CountryOfRisk AS ErrorValue
FROM [Cris_Ocean_tmp].[dbo].[NiiStressTest2018_v2_PUBLISHED_201812_CC_0326] 
WHERE 
    StressTestAccountEnabled LIKE 'Yes' 
    AND BalanceSheetAmount != 0 
    AND CountryOfRisk NOT LIKE '%[^a-z]%'  -- 修正原代码的笔误，应该判断CountryOfRisk而非Country
    AND (LEN(CountryOfRisk)>2 OR LEN(CountryOfRisk)<2)
UNION ALL
SELECT DISTINCT 
    'InterestType' AS SourceColumn,
    InterestType AS ErrorValue
FROM [Cris_Ocean_tmp].[dbo].[NiiStressTest2018_v2_PUBLISHED_201812_CC_0326] 
WHERE 
    StressTestAccountEnabled LIKE 'Yes' 
    AND BalanceSheetAmount <> 0 
    AND InterestType NOT LIKE 'Fixed' 
    AND InterestType NOT LIKE 'Floating'
UNION ALL
SELECT DISTINCT 
    'InterestRate' AS SourceColumn,
    InterestRate AS ErrorValue
FROM [Cris_Ocean_tmp].[dbo].[NiiStressTest2018_v2_PUBLISHED_201812_CC_0326] 
WHERE 
    BalanceSheetAmount <> 0 
    AND StressTestAccountEnabled LIKE 'Yes' 
    AND (InterestRate > 20 OR InterestRate < -2)
UNION ALL
SELECT DISTINCT 
    'LocalBalance' AS SourceColumn,
    LocalBalance AS ErrorValue
FROM [Cris_Ocean_tmp].[dbo].[NiiStressTest2018_v2_PUBLISHED_201812_CC_0326] 
WHERE 
    StressTestAccountEnabled LIKE 'Yes' 
    AND BalanceSheetAmount <> 0 
    AND LocalBalance IS NULL

关键改动说明 #

1. 新增来源列：每个子查询都添加了'列名' AS SourceColumn，用固定字符串明确标记当前错误的来源字段。
2. 统一错误值列名：把原来的各个字段统一命名为ErrorValue，这样UNION后的结果会有两列：SourceColumn（错误来源）和ErrorValue（具体错误内容）。
3. 修正逻辑优先级：给WHERE子句里的OR条件添加括号，避免因AND优先级高于OR导致的逻辑偏差（原代码的写法可能会把OR [1001account] IS NULL和前面的AND条件分开判断，不符合预期）。
4. 替换UNION为UNION ALL：如果不需要跨列去重相同的错误值，UNION ALL的执行效率更高；若需要全局去重，可换回UNION。
5. 修正笔误：CountryOfRisk的查询条件里，原代码误判了Country字段，已修正为判断CountryOfRisk。

预期输出示例 #

这样你就能一眼区分每个错误对应的来源列了。

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