sql - 带有空值的MS Sql连接

我有3张桌子，看起来像：
表格1：

╔════╦═══════╗
║ id ║ name  ║
╠════╬═══════╣
║  1 ║ name1 ║
╚════╩═══════╝

表2：

╔════╦════════════╗
║ id ║   data1    ║
╠════╬════════════╣
║  1 ║ some data1 ║
║  1 ║ some data2 ║
║  1 ║ some data3 ║
╚════╩════════════╝

表3：

╔════╦═══════╗
║ id ║ data2 ║
╠════╬═══════╣
║  1 ║   456 ║
║  1 ║   345 ║
╚════╩═══════╝

结果，我想加入一个表，如果某些表中没有这样的数据，该表将为空值。我想得到这样的东西：

╔════╦═══════╦════════════╦════════╗
║ id ║ name  ║   data1    ║  data2 ║
╠════╬═══════╬════════════╬════════╣
║  1 ║ name1 ║ some data1 ║ 456    ║
║  1 ║ name1 ║ some data2 ║ 345    ║
║  1 ║ name1 ║ some data3 ║ null   ║
╚════╩═══════╩════════════╩════════╝

我不知道该怎么办。我曾尝试使用外部联接，但结果屡屡发生。也许可以使用诸如group by或其他聚合函数之类的东西？

现在我的代码是：

SELECT * FROM Table1 t1
left outer join Table2 t2 on t1.id=t2.id
left outer join Table3 t3 on t1.id=t3.id

是否可以获得我想要的结果以及如何做到这一点？

最佳答案

首先，您需要从Table1重复每一行，最多重复N次，其中N是Table2和Table3中相关行的最大计数。这可以使用理货单来完成。

然后，使用ROW_NUMBER向Table2和Table3添加另一个ID，并在JOIN条件下使用该新创建的ID：

SQL Fiddle

DECLARE @maxCount INT

SELECT @maxCount =  MAX(cnt)
FROM (
    SELECT COUNT(*) AS cnt FROM Table1 GROUP BY id UNION ALL
    SELECT COUNT(*) AS cnt FROM Table2 GROUP BY id UNION ALL
    SELECT COUNT(*) AS cnt FROM Table3 GROUP BY id
) t

;WITH Tally AS(
    SELECT TOP (@maxCount)
        N = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
    FROM sys.all_columns a CROSS JOIN sys.all_columns b
),
Cte AS(
    SELECT t1.*, cnt = x.cnt
    FROM Table1 t1
    OUTER APPLY(
        SELECT TOP 1 cnt
        FROM (
            SELECT COUNT(*) AS cnt FROM Table1 WHERE id = t1.id UNION ALL
            SELECT COUNT(*) AS cnt FROM Table2 WHERE id = t1.id UNION ALL
            SELECT COUNT(*) AS cnt FROM Table3 WHERE id = t1.id
        ) t
        ORDER BY cnt DESC
    )x
),
CteTable1 AS(
    SELECT t1.*, rn = t.N
    FROM Cte t1
    CROSS JOIN Tally t
    WHERE t.N <= t1.cnt
),
CteTable2 AS(
    SELECT *,
        rn = ROW_NUMBER() OVER(PARTITION BY id ORDER BY data1)
    FROM Table2
),
CteTable3 AS(
    SELECT *,
        rn = ROW_NUMBER() OVER(PARTITION BY id ORDER BY data2)
    FROM Table3
)
SELECT
    t1.id, t1.name, t2.data1, t3.data2
FROM CteTable1 t1
LEFT JOIN CteTable2 t2
    ON t2.id = t1.id
    AND t2.rn = t1.rn
LEFT JOIN CteTable3 t3
    ON t3.id = t1.id
    AND t3.rn = t1.rn