SQL Server多视图UNION查询优化请求（用于PowerBI数据加载）

你这段SQL的核心问题是重复代码过多：两个UNION分支除了主表不同，字段、关联逻辑、过滤条件完全一致，不仅维护麻烦，还可能影响查询性能。下面是优化方案：

优化思路 #

1. 合并主表数据源：用UNION ALL先把两个主表的所有字段合并（两个表结构一致才能用UNION，这里默认结构匹配），比原代码的UNION性能更好（UNION会自动去重，不需要的话用ALL）。
2. 统一关联与过滤：把合并后的主表作为基础数据源，只写一次JOIN和WHERE条件，避免重复代码。
3. 简化过滤条件：提取两个分支共有的过滤条件，减少冗余判断。
4. 修复重复字段名：原代码里有两个[Due Date]字段，PowerBI加载时会报错或自动重命名，建议手动修改其中一个的别名，比如把b.GBC_SMFFUpDate改为[Follow-up Due Date]。

优化后的代码 #

WITH CombinedCases AS (
    -- 合并两个主表数据
    SELECT 
        SpecID,
        CreateDate,
        ModifyDate,
        CaseDesc,
        CaseMgrID,
        PriorCaseMgr,
        CaseSubTypeID,
        ExpectedFinishTime,
        CaseStatusRefID,
        ClientID,
        CountryID,
        GBC_SM_FFUp,
        GBC_SMFFUpDate,
        GBC_SMRemarks,
        Inventory_Status_PH_RefItem,
        Insurer_DataPH_RefItem,
        FirstFollowUp_DataPH,
        SecondFollowUp_DataPH,
        ThirdFollowUp_DataPH,
        AdditionalFollowUpDate_DataPH
    FROM [GBSNightlyBackupReport].[dbo].[CaseAddlDataMemberSupportStaffMovement]
    UNION ALL
    SELECT 
        SpecID,
        CreateDate,
        ModifyDate,
        CaseDesc,
        CaseMgrID,
        PriorCaseMgr,
        CaseSubTypeID,
        ExpectedFinishTime,
        CaseStatusRefID,
        ClientID,
        CountryID,
        GBC_SM_FFUp,
        GBC_SMFFUpDate,
        GBC_SMRemarks,
        Inventory_Status_PH_RefItem,
        Insurer_DataPH_RefItem,
        FirstFollowUp_DataPH,
        SecondFollowUp_DataPH,
        ThirdFollowUp_DataPH,
        AdditionalFollowUpDate_DataPH
    FROM [GBSNightlyBackupReport].[dbo].[CaseAddlDataMemberSupportEnrolmentRecon]
)
SELECT
    b.SpecID as CaseID,
    b.CreateDate,
    b.ModifyDate as LastModified,
    f.SpecName as Country,
    b.CaseDesc,
    a.displayname as AssignedTo,
    b.PriorCaseMgr as PreviousAsignee,
    e.SpecName as CaseSubtype,
    b.[ExpectedFinishTime] as [Due Date],
    d.refitem as [Status],
    c.SpecName as Client,
    b.GBC_SM_FFUp as [Number of Follow-ups], 
    b.GBC_SMFFUpDate as [Follow-up Due Date], -- 修复重复字段名
    b.GBC_SMRemarks as Remarks,
    b.Inventory_Status_PH_RefItem as [Inventory Status],
    b.Insurer_DataPH_RefItem as Insurer,
    b.FirstFollowUp_DataPH as [1st Follow Up Date],
    b.SecondFollowUp_DataPH as [2nd Follow Up Date],
    b.ThirdFollowUp_DataPH as [3rd Follow Up Date],
    b.AdditionalFollowUpDate_DataPH as [Additional Follow Up Dates],
    'Survey' as RecordType
FROM CombinedCases as b
LEFT JOIN [GBSNightlyBackupReport].dbo.[UserMain] as a on a.[UserID]=b.[CaseMgrID]
LEFT JOIN [GBSNightlyBackupReport].dbo.[_Client] as c on c.clientID = b.ClientID
LEFT JOIN [GBSNightlyBackupReport].dbo.[_ReferenceGroupRefGrpItemLanguage] as d on d.refitemid = b.CaseStatusRefID
LEFT JOIN [GBSNightlyBackupReport].dbo.[_Country] as f on f.SpecID = b.CountryID
LEFT JOIN [GBSNightlyBackupReport].dbo.[_CaseSubtype] as e on e._SpecID = b.CaseSubTypeID
WHERE 
    LanguageRefID = 0 
    AND f.SpecID = 408983
    AND (
        (refGrpID=15537532) 
        OR (refGrpID=15995780 and RefitemID=3453013)
    )

额外说明 #

• 如果两个主表存在重复数据需要去重，把UNION ALL改回UNION即可，但优先用ALL提升性能。
• 可以给CombinedCases的字段加上别名，让CTE更清晰，但原表字段名一致的话可以省略。
• 优化后的代码更易维护，后续修改字段或过滤条件只需改一处。

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