MSSQL Merge 3 層次表

我想將 3 個層次結構表合併到一個表中。

3張表：

CREATE TABLE dbo.tbl1 (
   key1 varchar(50) NULL,
   key2 varchar(50) NULL
) ON PRIMARY

CREATE TABLE dbo.tbl2 (
   key2 varchar(50) NULL,
   key3 varchar(50) NULL
) ON PRIMARY

CREATE TABLE dbo.tbl3 (
   key3 varchar(50) NULL,
   key4 varchar(50) NULL
) ON PRIMARY

一些樣本數據：

INSERT INTO tbl1 (key1, key2)
VALUES ('001','300'), ('001','301'),('005','405'),('006','406');

INSERT INTO tbl2 (key2, key3)
VALUES ('300','5554'), ('300','5555'),('405','5557'),('406','5563');

INSERT INTO tbl3 (key3, key4)
VALUES ('5554','9000'), ('5555','9005'),('5555','9006'),('5563','9007');

帶有數據的表格的視覺化表示：

       table 1                  table 2                  table 3
   +------+------+          +------+------+          +------+------+
   | key1 | key2 |          | key2 | key3 |          | key3 | key4 |
   +------+------+          +------+------+          +------+------+
   | 001  | 300  |          | 300  | 5554 |          | 5554 | 9000 |
   | 001  | 301  |          | 300  | 5555 |          | 5555 | 9005 |
   | 005  | 405  |          | 405  | 5557 |          | 5555 | 9006 |
   | 006  | 406  |          | 406  | 5563 |          | 5563 | 9007 |
   +------+------+          +------+------+          +------+------+

有人可以幫我加入上述表格以實現以下結構，請：

CREATE TABLE dbo.tbl4 (
   key1 varchar(50) NULL,
   key2 varchar(50) NULL,
   key3 varchar(50) NULL,
   key4 varchar(50) NULL
) ON PRIMARY

到目前為止我的查詢：

MERGE tbl4 t USING tbl1 s
   ON (s.key1 = t.key1)
   WHEN MATCHED THEN UPDATE SET
       t.key2 = s.key2
   WHEN NOT MATCHED BY TARGET
       THEN    INSERT(key1, key2)
               VALUES(s.key1, s.key2)
   WHEN NOT MATCHED BY SOURCE
       THEN DELETE;

上面的腳本只適用於第一個表。如果我嘗試修改上面的程式碼以將表 2 和 3 合併到最後一個，我會得到以下輸出：

Msg 8672, Level 16, State 1, Procedure dbo.spProcessPartCodes, Line 260 [Batch Start Line 2] The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

我想得到的輸出：

          table 4 (combined)
   +------+------+-------+-------+
   | key1 | key2 | key3  | key4  |
   +------+------+-------+-------+
   | 001  | 300  | 5554  | 9000  |
   | 001  | 300  | 5555  | 9005  |
   | 001  | 300  | 5555  | 9006  |
   | 001  | 301  | null  | null  |
   | 005  | 405  | 5557  | null  |
   | 006  | 406  | 5563  | 9007  |
   +------+------+-------+-------+

我遇到過工會和遞歸 cte，但到目前為止我沒有運氣實施它們。

任何幫助將不勝感激。

因為您有四個建立層次結構的表，您可以直接連接所有元素：

SELECT
 level1.key1
,level1.key2
,level2.key3
,level3.key4
FROM
 table1 level1
LEFT JOIN
 table2 level2
   ON level2.key2 = level1.key2
LEFT JOIN
 table3 level3
   ON level3.key3 = level2.key3

引用自：https://dba.stackexchange.com/questions/296266