使用查询中的行号连接2个表

Question

我需要连接两个表，这是复杂的。“契约”选项卡是两个表之间的链接。大型表是用行号查询的，小表可能只与输入'AI‘的位置连接，因为info选项卡包含我需要的数据。

这是大桌子

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'

结果大表

​

​

结果小表

​

​

Answer 1

在选择小表之前，可以将小表连接到大表。

SELECT x.*,
info.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
JOIN
(SELECT info1, left([key],9) as contract
FROM [hydra1].[hydadm].[v_hyinfo]
WHERE typ = 'AI'
) info ON x.contract = info.contract
WHERE    x.row_number = 1
ORDER BY x.a_status ASC
, x.erranf_dat ASC
, x.erranf_zeit ASC;