SQL Server SQL连接连接表的第一、第二和第三行

yruzcnhs 于 2022-12-26 发布在其他

关注(0)|答案(3)|浏览(182)

我需要显示来自连接的最终结果的一些列。这些列分为3组，例如：

name_1, addr_1, prefecture_1, price_1,
name_2, addr_2, prefecture_2, price_2,
name_3, addr_3, prefecture_3, price_3

其中，这3个组来自一个联接，该联接应根据排序键carrier_cd2仅返回3条记录。_1组是第一行，_2组是第二行，_3是第三行。
我的问题与this SO question几乎相同，但我需要3条记录而不是2条。
我已经提出了我的初始SQL查询：

SELECT
    inner_t12.contract_no,
    inner_t13.carrier_cd2,
    inner_t13.addr,
    inner_t13.prefecture,
    inner_t13_2.carrier_cd2,
    inner_t13_2.addr,
    inner_t13_2.prefecture,
    inner_t13_3.carrier_cd2,
    inner_t13_3.addr,
    inner_t13_3.prefecture
FROM
    処分業者契約マスタ inner_t12
    LEFT OUTER JOIN 処分契約委託運搬業者マスタ inner_t13
        ON inner_t12.contract_no = inner_t13.contract_no
    JOIN 処分契約委託運搬業者マスタ inner_t13_2
        ON inner_t13_2.contract_no = inner_t13.contract_no
        AND inner_t13_2.carrier_cd2 > inner_t13.carrier_cd2
    JOIN 処分契約委託運搬業者マスタ inner_t13_3
        ON inner_t13_3.contract_no = inner_t13.contract_no
        AND inner_t13_3.carrier_cd2 > inner_t13_2.carrier_cd2

截图
它返回最终结果集上所需的行，但是它需要是左外连接，以便即使连接没有生成所需的3行，它仍然会显示行，甚至2或1或无应该工作，并显示NULL代替。
希望你能在这方面给予点提示。
SQL服务器2005快速版

sql-server

来源：https://stackoverflow.com/questions/4048953/sql-joining-the-first-second-and-third-row-of-joining-table

3条答案

按热度按时间

dxxyhpgq1#

如果我理解了问题的意思，我想你是在找这个。把两个内联接括在左联接里面

FROM
    inner_t12
    LEFT OUTER JOIN inner_t13
    (    
    INNER JOIN  inner_t13_2
        ON inner_t13_2.contract_no = inner_t13.contract_no
        AND inner_t13_2.carrier_cd2 > inner_t13.carrier_cd2
    INNER JOIN  inner_t13_3
        ON inner_t13_3.contract_no = inner_t13.contract_no
        AND inner_t13_3.carrier_cd2 > inner_t13_2.carrier_cd2
)
ON inner_t12.contract_no = inner_t13.contract_no

赞(0）回复(0）举报 2022-12-26

laawzig22#

如果您的意思是只有3行（3条记录），只需在SELECT语句中添加"TOP 3"。
见下文。

SELECT
    TOP 3
    inner_t12.contract_no,
    inner_t13.carrier_cd2,
    inner_t13.addr,
    inner_t13.prefecture,
    inner_t13_2.carrier_cd2,
    inner_t13_2.addr,
    inner_t13_2.prefecture,
    inner_t13_3.carrier_cd2,
    inner_t13_3.addr,
    inner_t13_3.prefecture
FROM
    処分業者契約マスタ inner_t12
    LEFT OUTER JOIN 処分契約委託運搬業者マスタ inner_t13
        ON inner_t12.contract_no = inner_t13.contract_no
    JOIN 処分契約委託運搬業者マスタ inner_t13_2
        ON inner_t13_2.contract_no = inner_t13.contract_no
        AND inner_t13_2.carrier_cd2 > inner_t13.carrier_cd2
    JOIN 処分契約委託運搬業者マスタ inner_t13_3
        ON inner_t13_3.contract_no = inner_t13.contract_no
        AND inner_t13_3.carrier_cd2 > inner_t13_2.carrier_cd2

赞(0）回复(0）举报 2022-12-26

wmtdaxz33#

我想我已经解决了这个问题，我所做的就是在JOIN的ON子句上使用ROW_NUMBER和OVER（）。

use OurProjectDb
go

SELECT
    t12.disposal_cd,

    t21_1.name_kanji AS name_kanji_1,
    t13_1.prefecture AS prefecture_1,
    t13_1.addr AS addr_1,
    t13_1.permission_no AS permission_no_1,
    t13_1.vehicle_type AS vehicle_type_1,
    t13_1.transport_price AS transport_price_1,

    t21_2.name_kanji AS name_kanji_2,
    t13_2.prefecture AS prefecture_2,
    t13_2.addr AS addr_2,
    t13_2.permission_no AS permission_no_2,
    t13_2.vehicle_type AS vehicle_type_2,
    t13_2.transport_price AS transport_price_2,

    t21_3.name_kanji AS name_kanji_3,
    t13_3.prefecture AS prefecture_3,
    t13_3.addr AS addr_3,
    t13_3.permission_no AS permission_no_3,
    t13_3.vehicle_type AS vehicle_type_3,
    t13_3.transport_price AS transport_price_3
FROM
    処分業者契約マスタ t12
    LEFT OUTER JOIN
    (
        処分契約委託運搬業者マスタ t13_1
        LEFT OUTER JOIN 委託運搬業者マスタ t21_1 ON t13_1.carrier_cd2 = t21_1.carrier_cd2
    )
    ON t13_1.contract_no = t12.contract_no AND t13_1.carrier_cd2 = (
        SELECT TOP 1
            inner_t13_1.carrier_cd2
        FROM
            処分契約委託運搬業者マスタ inner_t13_1
        WHERE
            inner_t13_1.contract_no = t12.contract_no
        ORDER BY
            inner_t13_1.carrier_cd2 ASC
    )
    LEFT OUTER JOIN
    (
        処分契約委託運搬業者マスタ t13_2
        LEFT OUTER JOIN 委託運搬業者マスタ t21_2 ON t13_2.carrier_cd2 = t21_2.carrier_cd2
    )
    ON t13_2.contract_no = t12.contract_no AND t13_2.carrier_cd2 = (
        SELECT
            carrier_cd2
        FROM
        (
            SELECT TOP 2
                ROW_NUMBER() OVER (ORDER BY inner_t13_2.carrier_cd2) AS row_num,
                inner_t13_2.carrier_cd2
            FROM
                処分契約委託運搬業者マスタ inner_t13_2
            WHERE
                inner_t13_2.contract_no = t12.contract_no
            ORDER BY
                inner_t13_2.carrier_cd2
        ) top2join
        WHERE
            row_num = 2
    )
    LEFT OUTER JOIN
    (
        処分契約委託運搬業者マスタ t13_3
        LEFT OUTER JOIN 委託運搬業者マスタ t21_3 ON t13_3.carrier_cd2 = t21_3.carrier_cd2
    )
    ON t13_3.contract_no = t12.contract_no AND t13_3.carrier_cd2 = (
        SELECT
            carrier_cd2
        FROM
        (
            SELECT TOP 3
                ROW_NUMBER() OVER (ORDER BY inner_t13_3.carrier_cd2) AS row_num,
                inner_t13_3.carrier_cd2
            FROM
                処分契約委託運搬業者マスタ inner_t13_3
            WHERE
                inner_t13_3.contract_no = t12.contract_no
            ORDER BY
                inner_t13_3.carrier_cd2
        ) top3join
        WHERE
            row_num = 3
    )

赞(0）回复(0）举报 2022-12-26

我来回答

SQL Server SQL连接连接表的第一、第二和第三行

3条答案

相关问题

热门标签

最新问答