如何正确地在非主键上联接多个表？

Question

我正在尝试创建一个视图，让用户看到每个“批次”连接的一行，以便当来自不同表的“批次”匹配时，它们应该作为一行一起出现。但是，如果任何一个表本身就有“批次”，它也应该作为一行添加到结果中，并在其他列中填入“NULL”。

我认为问题出在如何连接这些表上。但是我还不能完全确定问题所在。

CREATE TABLE #ItemTable ([Item] nvarchar(16))
CREATE TABLE #LocationTable ([Item] nvarchar(16), [Batch] nvarchar(32), [Location] nvarchar(13), [Quantity] int)
CREATE TABLE #OrderTable ([Item] nvarchar(16), [Batch] nvarchar(32), [Quantity] int)
CREATE TABLE #BookingTable ([Item] nvarchar(16), [Batch] nvarchar(32), [Quantity] int)

--------------------------------------------------------------------------------------------------
-- CURRENT RESULT:
--------------------------------------------------------------------------------------------------
--  Item    Batch   Location        QuantityOnLocation  OrderedQuantity BookedQuantity
--  1000    1       Location_1      10                  NULL            NULL
--  1000    22      Location_2      10                  NULL            NULL
--  2000    333     Location_3      0                   10              NULL
--  2000    4444    Location_4      10                  NULL            NULL
--  3000    666666  NULL            NULL                10              10

--------------------------------------------------------------------------------------------------
-- DESIRED RESULT:
--------------------------------------------------------------------------------------------------
--  Item    Batch   Location        QuantityOnLocation  OrderedQuantity BookedQuantity
--  1000    1       Location_1      10                  NULL            10
--  1000    22      Location_2      10                  NULL            0
--  1000    55555   NULL            NULL                NULL            10
--  2000    333     Location_3      0                   10              NULL
--  2000    4444    Location_4      10                  NULL            NULL
--  3000    666666  NULL            NULL                10              10


INSERT INTO #ItemTable ([Item]) VALUES 
('1000'), 
('2000'), 
('3000')

INSERT INTO #LocationTable ([Item], [Batch], [Location], [Quantity]) VALUES 
('1000', '1', 'Location_1', 10), 
('1000', '22', 'Location_2', 10), 
('2000', '333', 'Location_3', 0),
('2000', '4444', 'Location_4', 10)

INSERT INTO #OrderTable ([Item], [Batch], [Quantity]) VALUES 
('2000', '333', 10), 
('3000', '666666', 10)

INSERT INTO #BookingTable ([Item], [Batch], [Quantity]) VALUES 
('1000', '1', 10), 
('1000', '55555', 10), 
('3000', '666666', 10)


SELECT 
    [Item].[Item] AS [Item], 
    COALESCE([Location].[Batch], [Order].[Batch], [Booking].[Batch]) AS [Batch],
    [Location].[Location] AS [Location], 
    [Location].[Quantity] AS [QuantityOnLocation],
    [Order].[Quantity] AS [OrderedQuantity],
    [Booking].Quantity AS [BookedQuantity]
FROM 
    #ItemTable AS [Item]
    LEFT OUTER JOIN (
        SELECT [Item], [Quantity], [Batch], [Location]
        FROM #LocationTable)
    AS [Location] ON [Location].[Item] = [Item].[Item] 

    LEFT OUTER JOIN (
        SELECT [Item], [Quantity], [Batch]
        FROM #OrderTable) 
    AS [Order] ON [Order].[Item] = [Item].[Item] 
        AND ISNULL([Order].[Batch], '') = ISNULL([Location].[Batch], [Order].[Batch]) 

    LEFT OUTER JOIN (
        SELECT [Item], [Quantity], [Batch]
        FROM #BookingTable) 
    AS [Booking] ON [Order].[Item] = [Item].[Item]
        AND ISNULL([Booking].[Batch], '') = COALESCE([Location].[Batch], [Order].[Batch], [Booking].[Batch]) 
WHERE
    ISNULL([Location].[Quantity], 0) <> 0
    OR ISNULL([Order].[Quantity], 0) <> 0
    OR ISNULL([Booking].Quantity, 0) <> 0


DROP TABLE #ItemTable
DROP TABLE #LocationTable
DROP TABLE #BookingTable 
DROP TABLE #OrderTable

- Danieboy

Answer 1

您在最后一个连接中打错了一个字（我认为），这一部分应该是：

LEFT OUTER JOIN (
    SELECT [Item], [Quantity], [Batch]
    FROM #BookingTable) 
AS [Booking] ON [Order].[Item] = [Item].[Item]

这句话不应该是这样吗：

ON [Booking].[Item] = [Item].[Item]

我稍微修改了您的查询，改成了这样：

SELECT 
    i.Item AS Item, 
    COALESCE(l.Batch, o.Batch, b.Batch) AS Batch,
    l.Location AS Location, 
    l.Quantity AS QuantityOnLocation,
    o.Quantity AS OrderedQuantity,
    b.Quantity AS BookedQuantity
FROM 
    #ItemTable i
    LEFT JOIN #LocationTable l ON l.Item = i.Item
    LEFT JOIN #OrderTable o ON o.Item = i.Item AND o.Batch = ISNULL(l.Batch, o.Batch) 
    LEFT JOIN #BookingTable b ON b.Item = i.Item AND b.Batch = COALESCE(l.Batch, o.Batch, b.Batch) 
WHERE
    ISNULL(l.Quantity, 0) != 0
    OR ISNULL(o.Quantity, 0) != 0
    OR ISNULL(b.Quantity, 0) != 0;

我认为第一个选项更易读，但这可能是个人偏好。

然后我意识到这仍然不能满足你的需求，所以我再次重构代码得到了以下结果（可以达到你想要的效果）：

WITH UniqueItemBatch AS (
    SELECT DISTINCT Item, Batch FROM #LocationTable
    UNION
    SELECT DISTINCT Item, Batch FROM #OrderTable
    UNION
    SELECT DISTINCT Item, Batch FROM #BookingTable)
SELECT 
    u.Item AS Item, 
    u.Batch,
    l.Location AS Location, 
    l.Quantity AS QuantityOnLocation,
    o.Quantity AS OrderedQuantity,
    b.Quantity AS BookedQuantity
FROM
    UniqueItemBatch u
    LEFT JOIN #ItemTable i ON i.Item = u.Item
    LEFT JOIN #LocationTable l ON l.Item = u.Item AND l.Batch = u.Batch
    LEFT JOIN #OrderTable o ON o.Item = u.Item AND o.Batch = u.Batch
    LEFT JOIN #BookingTable b ON b.Item = u.Item AND b.Batch = u.Batch
WHERE
    ISNULL(l.Quantity, 0) != 0
    OR ISNULL(o.Quantity, 0) != 0
    OR ISNULL(b.Quantity, 0) != 0;

- Richard Hansell

---

这似乎是一个不错的解决方案。如果添加另一个表（例如Booking），它将如何扩展？只需添加LEFT JOIN和SELECT DISTINCT吗？（当然，还要考虑选择的位置） - Danieboy

我尝试了一下，即使有更多的表格，它似乎也能给出我想要的结果，并且很容易添加更多。明天只需进行更多测试，看看是否存在任何不起作用的情况，然后再标记为答案。 - Danieboy

是的，这个方案应该可以扩展到更多的表格，因为它将工作分解成两个部分，一个部分确定唯一的项目/批次ID，另一个部分带入任何匹配的数据。如果您使用原始解决方案，将会通过添加更多的表格来得到一些非常长的COLLATE语句，并且出错的可能性也更大。我的方法唯一的缺点是性能可能不如“单次查询”？然而，优化器可能不会按照纸面上的方式运行它...所以最好进行测试。 - Richard Hansell

COALESCE 不是 COLLATE。 - Richard Hansell

还有一件事需要注意，你完全可以删除与#ItemTable的LEFT JOIN，因为它现在是多余的；这个表中没有任何列包含在查询中，并且它是一个LEFT JOIN，所以它不起约束作用。我想无论如何都会进行优化处理？ - Richard Hansell

是的，这对我来说完美地解决了问题。我将在真实（未清理）的应用程序中保留ItemTable。感谢您的帮助！ - Danieboy

Answer 2

我不确定你最后一列的逻辑是什么，但这样可以得到其他列所需的结果。

为了让你的查询基于预订或位置显示为批处理，我在查询中将两个表联合起来。

如果可能的话，我建议重新审视你的数据结构设计。

select 
    item.Item,
    batch.Batch, 
    max(batch.location) as location,
    sum(batch.LQuantity) as QuantityOnLocation,
    orders.Quantity as OrderedQuantity,
    sum(batch.BQuantity) as BookedQuantity
from
    #ItemTable item
        left join 
        (
            select Item, Batch, quantity as BQuantity, null as Location, null  as LQuantity from #BookingTable
            union
            select item, Batch, null, Location, Quantity as LQuantity from #LocationTable
        ) batch
            on item.Item = batch.Item
    left join #OrderTable orders
        on item.Item = orders.Item and batch.Batch = orders.Batch
group by 
    item.Item,
    batch.Batch,    
    orders.Quantity

- podiluska

---

很遗憾，我无法更改结构的设计，因为太多程序依赖于它。 - Danieboy

这个解决方案会如何“扩展”？我的意思是，如果有另一个类似Booking的表，会怎样？ - Danieboy

不知道涉及到的其他变量，很难说。 - podiluska

基本上是复制Booking并在结果中添加另一列。 - Danieboy

Answer 3

我认为我更喜欢不同的方法。你可以将所有批次号添加为空列，然后从每个表单更新不同的列。

像这样：

SELECT INTO #MyTableResult Batch FROM TableA
UNION 
SELECT Batch FROM TableB

等等。联合体已经移除了重复项。

然后你可以像这样更新：

Update #MyTableResult SET Column A = ValueA FROM TableA WHERE 
#MyTableResult.Batch = TableA.Batch.

当你的表格中所有更新都完成后，你就会得到期望的结果。

- SQL_M

---

为什么要使用临时表，而不是直接使用连接？我看到很多 BI 开发人员并不是很理解连接的原理，因此会采用这种方式，从而给生产服务器带来可怕的低效工作负载。 - Charleh

@Charleh：原因在于逻辑易于阅读和调整。我想这也取决于表的大小。在我看来，在许多情况下，这不是一个坏选择。运行一些性能测试，你就会清楚地了解这是否是这种特定情况的好解决方案。 - SQL_M