SQL左连接保留hpd_help_desk全量记录并按规则去重咨询

• 查询需返回hpd_help_desk表全部记录，不得因关联逻辑丢失主表数据
• 解决单条hpd_help_desk.INCIDENT_NUMBER关联hpd_associations、chg_infrastructure_change产生的重复行问题
• hpd_associations仅作为中间关联表，用于拉取chg_infrastructure_change对应数据

• 优先保留匹配项：chg_infrastructure_change.chg_request含CRQ编号、且关联的hpd_associations.association_type01 = 17000的记录
• 若单条INCIDENT_NUMBER无符合上述CRQ+17000条件的匹配项，仅取关联结果的第一条记录即可

• WHERE条件中直接写hpd_associations.association_type01 = 17000会将左连接转为内连接，过滤掉无17000类型关联的工单，不符合返回全部主表记录的要求
• 仅使用distinct(INCIDENT_NUMBER)无法实现按优先级去重的规则，会随机返回重复行中的一条，无法保证优先取17000+CRQ匹配记录

使用窗口函数按优先级排序后取每个工单的第一条关联记录，完全匹配去重规则：

WITH ranked_relation AS (
    SELECT
        ha.request_id02 AS incident_number,
        ha.association_type01 AS HPD_ASSOCIATIONS_Request,
        cic.request_id AS CHG_Request,
        cic.status_reason AS CHG_Status_Reason,
        cic.description2 AS CHG__Desc2,
        cic.infrastructure_change_id AS CHG_infrastructure_change_id,
        ROW_NUMBER() OVER (
            PARTITION BY ha.request_id02
            ORDER BY
                -- 优先级1：17000关联类型+CRQ编号的记录排第一位
                CASE WHEN ha.association_type01 = 17000 AND cic.request_id LIKE '%CRQ%' THEN 1 ELSE 2 END,
                -- 无匹配项时可按业务字段排序取第一条，示例按关联创建时间倒序取最新记录
                ha.start_date_01 DESC
        ) AS rn
    FROM helix_access.hpd_associations ha
    LEFT JOIN helix_access.chg_infrastructure_change cic
        ON ha.request_id01 = cic.infrastructure_change_id
)
SELECT
    hpd.INCIDENT_NUMBER,
    rr.HPD_ASSOCIATIONS_Request,
    rr.CHG_Request,
    rr.CHG_Status_Reason,
    rr.CHG__Desc2,
    rr.CHG_infrastructure_change_id
FROM helix_access.hpd_help_desk hpd
LEFT JOIN ranked_relation rr
    ON hpd.incident_number = rr.incident_number AND rr.rn = 1

可以通过子查询提前筛选符合条件的记录，但你提供的IN子句写法存在逻辑问题：

• IN子句会将主表结果限制为「存在17000类型关联的工单」，会丢失无对应关联的主表记录，不符合返回全部hpd_help_desk记录的要求
• 正确的预筛选逻辑应放在左连接的子查询内部，不会过滤主表数据，同时能减少关联计算的数据量，参考写法如下：

SELECT
    hpd.INCIDENT_NUMBER,
    rr.HPD_ASSOCIATIONS_Request,
    rr.CHG_Request,
    rr.CHG_Status_Reason,
    rr.CHG__Desc2,
    rr.CHG_infrastructure_change_id
FROM helix_access.hpd_help_desk hpd
LEFT JOIN (
    SELECT
        ha.request_id02 AS incident_number,
        ha.association_type01 AS HPD_ASSOCIATIONS_Request,
        cic.request_id AS CHG_Request,
        cic.status_reason AS CHG_Status_Reason,
        cic.description2 AS CHG__Desc2,
        cic.infrastructure_change_id AS CHG_infrastructure_change_id,
        ROW_NUMBER() OVER (
            PARTITION BY ha.request_id02
            ORDER BY CASE WHEN ha.association_type01 = 17000 AND cic.request_id LIKE '%CRQ%' THEN 1 ELSE 2 END
        ) AS rn
    FROM helix_access.hpd_associations ha
    LEFT JOIN helix_access.chg_infrastructure_change cic
        ON ha.request_id01 = cic.infrastructure_change_id
    -- 可在此处添加时间等预筛选条件，减少计算量
    -- WHERE From_unixtime(Cast(ha.start_date_01 AS BIGINT),'yyyy-MM-dd HH:mm:ss') >= '2022-03-01 00:00:00'
) rr ON hpd.incident_number = rr.incident_number AND rr.rn = 1

• 存在17000类型关联且对应CHG表含CRQ编号的工单，会优先返回该条匹配记录，同工单下其余重复关联行被过滤
• 无符合17000+CRQ条件关联的工单，仅返回排序后的第一条关联记录，完全匹配你给出的Keep/Remove规则
• hpd_help_desk所有记录都会保留，不会因关联逻辑丢失主表数据

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