具有多個一對多關系的復雜連接

我想提取一些帶有相關專案和注釋的訂單。

每個訂單可以有一個或多個專案，沒有或多個注釋。

這是一些示例資料；

訂單：

專案：

筆記：

這是我期望輸出的樣子：

我嘗試了很多使用交叉應用和排名的方法，但都無法達到我想要的結果。

這是其中一種技術的示例，該技術有效但很混亂，最多只能用于 5 個專案或筆記。我認為引入 CTE 可能是答案？

SELECT
    , ER101_ORD_NBR
    , ER101_DESC
    , RANK () OVER (PARTITION BY ER101_ORG_CODE, ER101_EVT_ID, ER101_ORD_NBR ORDER BY ER101_DESC)
        Order_Desc_Rank
INTO
    #order_items
FROM
    ER101_ACCT_ORDER_DTL

SELECT
    , CC025_ORDER
    , CC025_NOTE_TEXT
    , RANK () OVER (ORDER BY CC025_NOTE_TEXT)
        Note_Rank
INTO
    #notes
FROM
    CC025_NOTES_EXT
    
DECLARE @i_items INT, @i_notes INT
SELECT @i_items = MAX(Order_Desc_Rank) FROM #order_items
SELECT @i_notes = MAX(Note_Rank) FROM #notes

SELECT
    ER100_ORD_NBR
    , ER101_DESC
    , CC025_NOTE_TEXT
FROM
    ER100_ACCT_ORDER
CROSS APPLY
(
    SELECT 1 _RANK
    UNION ALL SELECT 2 WHERE @i_items >= 2 OR @i_notes >= 2
    UNION ALL SELECT 3 WHERE @i_items >= 3 OR @i_notes >= 3
    UNION ALL SELECT 4 WHERE @i_items >= 4 OR @i_notes >= 4
    UNION ALL SELECT 5 WHERE @i_items >= 5 OR @i_notes >= 5
) ZZZ
OUTER APPLY
(
    SELECT
        *
    FROM
        #order_items
    WHERE
        ER100_ORD_NBR = ER101_ORD_NBR   
        AND ZZZ._RANK = Order_Desc_Rank
) ER101
OUTER APPLY
(
    SELECT
        *
    FROM
        #notes
    WHERE
        CC025_ORDER = ER100_ORD_NBR
        AND Note_Rank = ZZZ._RANK
) CC025

uj5u.com熱心網友回復：

您在這里似乎沒有可用于訂購的列，因此我決定對相關表格使用說明和注釋。我不得不利用ROW_NUMBER來產生一個用于訂購的價值。如果你的真實資料有你可以使用的東西，你可以跳過這一步。這是一個完整的作業示例，說明如何使用完整的外部聯接來完成此操作。還要注意我是如何創建表和示例資料的。這就是您將來應該如何發布您的問題的方式，因為它可以方便其他人提供幫助。

declare @Orders table 
(
    OrderNumber int
)
insert @Orders values (1), (2), (3)

declare @Items table 
(
    OrderNumber int
    , Description varchar(20)
)
insert @Items values
(1, 'Laptop')
, (1, 'TV')
, (2, 'Projector')
, (3, 'Laptop')
, (3, 'Projector')


declare @Notes table 
(
    OrderNumber int
    , Note varchar(100)
)
insert @Notes values
(2, 'Wheelchair access is required')
, (2, 'Client will be on site 2 hrs before doors open')
, (3, 'Tea and coffee is to be served at 10am')
;

with cteItems as
(
    select i.OrderNumber
        , i.Description
        , RowNum = ROW_NUMBER() over(partition by o.OrderNumber order by i.Description)
    from @Orders o
    join @Items i on i.OrderNumber = o.OrderNumber
)
, cteNotes as
(
    select n.OrderNumber
        , n.Note
        , RowNum = ROW_NUMBER() over(partition by o.OrderNumber order by n.Note)
    from @Orders o
    join @Notes n on n.OrderNumber = o.OrderNumber
)
select OrderNumber = coalesce(i.OrderNumber, n.OrderNumber)
    , i.Description
    , n.Note
from cteItems i
full outer join cteNotes n on i.OrderNumber = n.OrderNumber and i.RowNum = n.RowNum
 order by coalesce(i.OrderNumber, n.OrderNumber)

uj5u.com熱心網友回復：

為了防止由于可能有多個注釋而導致輸出中的笛卡爾結果，您需要將它們預先聚合到每個訂單的單行中。如果您要查找的所有專案都是逗號串列，那么同樣適用。然后，剩下的應該是一個簡單的連接。

select
      o.orderNumber,
      i.NumberItems,
      i.AllItems, 
      coalesce( n.NumberNotes, 0 ) NumberNotes,
      coalesce( n.AllNotes, '' ) AllNotes
   from
      orders o

         JOIN
         ( select
                 orderNumber,
                 count(*) NumberItems,
                 string_agg( description, ', ') AllItems
              from
                 items
              group by 
                 orderNumber ) i
           on o.orderNumber = i.orderNumber

         LEFT JOIN
         ( select
                 orderNumber,
                 count(*) NumberNotes,
                 string_agg( note, ', ') AllNotes
              from
                 notes
              group by 
                 orderNumber ) n
            on o.orderNumber = n.orderNumber

我對注釋的預聚合進行了 LEFT-JOIN，因為它們不是必需的，但對專案進行了正常連接，因為這是任何實際訂單的基礎。

uj5u.com熱心網友回復：

你試過這個嗎？

SELECT o.OrderNumber
, i.Description AS Item
, n.Note
FROM Orders o 
    INNER JOIN Items i ON 
        i.OrderNumber = o.OrderNumber
    LEFT JOIN Notes n ON 
        n.OrderNumber = o.OrderNumber

標籤：sql sql服务器

上一篇：procsql根據缺少值的條件左連接

下一篇：將每個重復行匹配到不同的記錄