如何使用UNION ALL与GROUP BY合并三个TSQL查询语句

现有3个可独立执行的TSQL查询语句，此前已在Microsoft Access中通过指定SQL完成合并，现需在TSQL环境中实现相同的合并逻辑，需注意ap.property_id与af1.property_id来自不同的数据表。具体合并需求及三个子查询语句如下：

Access中使用的合并语句 #

SELECT property_id
FROM [Insured (see TSQL Statement 1)] 
GROUP BY property_id
UNION ALL
SELECT property_id
FROM [Uninsured (see TSQL Statement 2)] 
GROUP BY property_id
UNION ALL
SELECT property_id
FROM [IE > 90 Days (see TSQL Statement 3)] 
GROUP BY property_id;

TSQL语句1（已投保） #

SELECT
    rtrim(STR_REPLACE(ap.region_name,' Region','')) AS 'Region',
    ap.property_id,
    ap.is_under_management_ind,
    ap.is_insured_ind,
    ap.has_active_assistance_ind,
    af1.is_pipeline_ind,

    CASE
        WHEN ap.is_insured_ind = 'Y' THEN 'Insured'
        ELSE 'Other'
    END AS 'Classification 1',

    CASE
        WHEN ap.is_insured_ind = 'Y' AND ap.is_under_management_ind = 'Y' AND af1.is_pipeline_ind = 'N' THEN 'Insured Only'
        WHEN ap.is_insured_ind = 'Y' AND ap.is_under_management_ind = 'Y' AND af1.is_pipeline_ind = 'Y' AND ap.has_active_assistance_ind = 'Y' THEN 'Insured and Assisted'
        ELSE 'Other Insured'
    END AS 'Classification 2'

FROM rems_dmart.dbo.active_financing af1
    INNER JOIN rems_dmart.dbo.active_property ap
    ON af1.property_id = ap.property_id

GROUP BY
    ap.region_name,
    ap.property_id,
    ap.is_under_management_ind,
    ap.is_insured_ind,
    ap.has_active_assistance_ind,
    af1.is_pipeline_ind

HAVING
    ( ap.region_name <> 'OHP'
    AND ap.is_under_management_ind = 'Y'
    AND ap.is_insured_ind = 'Y'
    AND af1.is_pipeline_ind = 'N' )
    OR
    ( ap.region_name <> 'OHP'
    AND ap.is_under_management_ind = 'Y'
    AND ap.is_insured_ind = 'Y'
    AND af1.is_pipeline_ind = 'Y'
    AND ap.has_active_assistance_ind = 'Y' )

TSQL语句2（未投保） #

SELECT
    rtrim(STR_REPLACE(ap.region_name,' Region','')) AS 'Region',
    ap.property_id,
    ap.is_under_management_ind,
    ap.is_insured_ind,
    ap.has_use_restriction_ind,
    ap.has_active_irp_ind,
    ap.has_active_assistance_ind,
    ap.is_service_coordinator_ind,

    CASE
        WHEN ap.is_insured_ind = 'N' THEN 'Uninsured'
        ELSE 'Other'
    END AS 'Classification 1',

    CASE
        WHEN ap.is_insured_ind = 'N' AND ap.has_active_assistance_ind = 'Y' THEN 'Assisted Only'
        WHEN ap.is_insured_ind = 'N' AND ap.has_active_assistance_ind = 'N' AND ap.has_use_restriction_ind = 'Y' THEN 'Use Agreement Only'
        WHEN ap.is_insured_ind = 'N' AND ap.has_active_assistance_ind = 'N' AND ap.has_use_restriction_ind = 'N' AND ap.has_active_irp_ind = 'Y' AND ap.is_service_coordinator_ind = 'N' THEN 'IRP'
        WHEN ap.is_insured_ind = 'N' AND ap.has_active_assistance_ind = 'N' AND ap.has_use_restriction_ind = 'N' AND ap.has_active_irp_ind = 'N' AND ap.is_service_coordinator_ind = 'Y' THEN 'Service Coordinator'
        WHEN ap.is_insured_ind = 'N' AND ap.has_active_assistance_ind = 'N' AND ap.has_use_restriction_ind = 'N' AND ap.has_active_irp_ind = 'Y' AND ap.is_service_coordinator_ind = 'Y' THEN 'IRP & Service Coordinator'
        ELSE 'Other Uninsured'
    END AS 'Classification 2'

FROM rems_dmart.dbo.active_property ap

GROUP BY
    ap.region_name,
    ap.property_id,
    ap.is_under_management_ind,
    ap.is_insured_ind,
    ap.has_use_restriction_ind,
    ap.has_active_irp_ind,
    ap.has_active_assistance_ind,
    ap.is_service_coordinator_ind

HAVING
    ( ap.region_name <> 'OHP'
    AND ap.is_under_management_ind = 'Y'
    AND ap.is_insured_ind = 'N'
    AND ap.has_active_assistance_ind = 'Y' )
    OR 
    ( ap.region_name <> 'OHP'
    AND ap.is_insured_ind = 'N'
    AND ap.has_use_restriction_ind = 'Y' )

TSQL语句3（IE超90天） #

SELECT
    rtrim(STR_REPLACE(ap.region_name,' Region','')) AS 'Region',
    af1.property_id,
    af1.initial_endorsement_date,
    af1.final_endorsement_date,
    ap.is_under_management_ind,

    CASE
        WHEN af1.initial_endorsement_date IS NOT NULL AND af1.final_endorsement_date IS NULL THEN 'IE > 90 Days'
        ELSE 'Other'
    END AS 'Classification 1',

    CASE
        WHEN af1.initial_endorsement_date IS NOT NULL AND af1.final_endorsement_date IS NULL AND ap.is_under_management_ind = 'Y' THEN 'IE > 90 Days_Under Mgmt'
        WHEN af1.initial_endorsement_date IS NOT NULL AND af1.final_endorsement_date IS NULL AND ap.is_under_management_ind = 'N' THEN 'IE > 90 Days_Not Under Mgmt'
        ELSE 'Other IE > 90 Days'
    END AS 'Classification 2'

FROM rems_dmart.dbo.active_property ap
    INNER JOIN rems_dmart.dbo.active_financing af1
        ON ap.property_id = af1.property_id

WHERE
    ( ap.region_name <> 'OHP'
    AND DATEDIFF(DAY, af1.initial_endorsement_date, CONVERT(VARCHAR(20), GETDATE(), 101)) > 90
    AND af1.final_endorsement_date IS NULL 
    AND ap.is_under_management_ind = 'N' )
    OR 
    ( ap.region_name <> 'OHP'
    AND DATEDIFF(DAY, af1.initial_endorsement_date, CONVERT(VARCHAR(20), GETDATE(), 101)) > 90
    AND af1.final_endorsement_date IS NULL 
    AND ap.is_under_management_ind = 'Y' )

最终TSQL合并实现语句 #

直接将三个子查询作为数据源，提取property_id后通过UNION ALL合并，与Access逻辑一致：

SELECT property_id
FROM (
    -- 已投保查询
    SELECT ap.property_id
    FROM rems_dmart.dbo.active_financing af1
        INNER JOIN rems_dmart.dbo.active_property ap
        ON af1.property_id = ap.property_id
    GROUP BY ap.property_id, ap.region_name, ap.is_under_management_ind, ap.is_insured_ind, ap.has_active_assistance_ind, af1.is_pipeline_ind
    HAVING
        ( ap.region_name <> 'OHP'
        AND ap.is_under_management_ind = 'Y'
        AND ap.is_insured_ind = 'Y'
        AND af1.is_pipeline_ind = 'N' )
        OR
        ( ap.region_name <> 'OHP'
        AND ap.is_under_management_ind = 'Y'
        AND ap.is_insured_ind = 'Y'
        AND af1.is_pipeline_ind = 'Y'
        AND ap.has_active_assistance_ind = 'Y' )
) AS Insured
GROUP BY property_id

UNION ALL

SELECT property_id
FROM (
    -- 未投保查询
    SELECT ap.property_id
    FROM rems_dmart.dbo.active_property ap
    GROUP BY ap.property_id, ap.region_name, ap.is_under_management_ind, ap.is_insured_ind, ap.has_use_restriction_ind, ap.has_active_irp_ind, ap.has_active_assistance_ind, ap.is_service_coordinator_ind
    HAVING
        ( ap.region_name <> 'OHP'
        AND ap.is_under_management_ind = 'Y'
        AND ap.is_insured_ind = 'N'
        AND ap.has_active_assistance_ind = 'Y' )
        OR 
        ( ap.region_name <> 'OHP'
        AND ap.is_insured_ind = 'N'
        AND ap.has_use_restriction_ind = 'Y' )
) AS Uninsured
GROUP BY property_id

UNION ALL

SELECT property_id
FROM (
    -- IE超90天查询
    SELECT af1.property_id
    FROM rems_dmart.dbo.active_property ap
        INNER JOIN rems_dmart.dbo.active_financing af1
            ON ap.property_id = af1.property_id
    WHERE
        ( ap.region_name <> 'OHP'
        AND DATEDIFF(DAY, af1.initial_endorsement_date, GETDATE()) > 90
        AND af1.final_endorsement_date IS NULL 
        AND ap.is_under_management_ind = 'N' )
        OR 
        ( ap.region_name <> 'OHP'
        AND DATEDIFF(DAY, af1.initial_endorsement_date, GETDATE()) > 90
        AND af1.final_endorsement_date IS NULL 
        AND ap.is_under_management_ind = 'Y' )
) AS IE_Over_90_Days
GROUP BY property_id;

注：优化了原语句中DATEDIFF的日期转换，直接使用GETDATE()无需转成字符串，避免潜在的日期格式问题；同时保留了原Access中每个分组后取property_id的逻辑。

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