如何通过Contract关联带Row Number的大表与AI类型小表？

需求说明 #

• 以Contract字段为关联键，关联两张数据表
• 大表处理规则：通过ROW_NUMBER()函数按b.aunr分区、erranf_zeit排序，仅保留行号为1的数据
• 小表处理规则：仅筛选typ='AI'的记录参与关联，该表包含业务所需的额外信息

大表查询语句 #

SELECT *
FROM (
    SELECT 
        z.plan_auftrag, 
        b.aunr as contract,
        b.user_n_07,
        b.user_c_47,
        b.user_n_08,
        b.erranf_dat,
        b.erranf_zeit,
        s.a_status,
        b.user_f_25,
        b.user_f_26,
        b.user_c_56,
        b.soll_menge_pri,
        b.user_c_49, 
        b.kunden_bez, 
        ROW_NUMBER() OVER (PARTITION BY b.aunr ORDER BY b.erranf_zeit) AS [row_number]
    FROM [hydra1].[hydadm].[v_auftrags_zusatz] z
    JOIN [hydra1].[hydadm].[auftrags_bestand] b
        ON z.auftrag_nr = b.aunr
    JOIN [hydra1].[hydadm].[v_auftrag_status] s
        ON b.auftrag_nr = s.auftrag_nr
    JOIN [hydra1].[hydadm].[mlst_hy] m
        ON s.auftrag_nr = m.auftrag_nr
        AND s.masch_nr = 'FIMI3'
        AND s.a_status IN ('V','L','U')
        AND m.kennz = 'M'
        AND s.eingeplant = ('M')
        AND b.a_typ IN ('AU','AG')
) AS x
WHERE x.row_number = 1
ORDER BY x.a_status ASC
    , x.erranf_dat ASC
    , x.erranf_zeit ASC;

小表查询语句 #

SELECT info1, left([key],9) as contract
FROM [hydra1].[hydadm].[v_hyinfo]
WHERE typ = 'AI'

查询结果 #

大表查询结果 #

小表查询结果 #

关联后的完整查询语句 #

以下是通过LEFT JOIN保留大表全部数据的关联实现（若仅需匹配数据，可替换为INNER JOIN）：

SELECT x.*, y.info1
FROM (
    SELECT 
        z.plan_auftrag, 
        b.aunr as contract,
        b.user_n_07,
        b.user_c_47,
        b.user_n_08,
        b.erranf_dat,
        b.erranf_zeit,
        s.a_status,
        b.user_f_25,
        b.user_f_26,
        b.user_c_56,
        b.soll_menge_pri,
        b.user_c_49, 
        b.kunden_bez, 
        ROW_NUMBER() OVER (PARTITION BY b.aunr ORDER BY b.erranf_zeit) AS [row_number]
    FROM [hydra1].[hydadm].[v_auftrags_zusatz] z
    JOIN [hydra1].[hydadm].[auftrags_bestand] b
        ON z.auftrag_nr = b.aunr
    JOIN [hydra1].[hydadm].[v_auftrag_status] s
        ON b.auftrag_nr = s.auftrag_nr
    JOIN [hydra1].[hydadm].[mlst_hy] m
        ON s.auftrag_nr = m.auftrag_nr
        AND s.masch_nr = 'FIMI3'
        AND s.a_status IN ('V','L','U')
        AND m.kennz = 'M'
        AND s.eingeplant = ('M')
        AND b.a_typ IN ('AU','AG')
) AS x
LEFT JOIN (
    SELECT info1, left([key],9) as contract
    FROM [hydra1].[hydadm].[v_hyinfo]
    WHERE typ = 'AI'
) AS y ON x.contract = y.contract
WHERE x.row_number = 1
ORDER BY x.a_status ASC
    , x.erranf_dat ASC
    , x.erranf_zeit ASC;

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