协慌网

我将使用一个具体的但假设的示例。

每个订单通常只有一个订单项：

命令：

OrderGUID   OrderNumber
=========   ============
{FFB2...}   STL-7442-1      
{3EC6...}   MPT-9931-8A

LineItems：

LineItemGUID   Order ID Quantity   Description
============   ======== ========   =================================
{098FBE3...}   1        7          prefabulated amulite
{1609B09...}   2        32         spurving bearing

但偶尔会有一个包含两个订单项的订单：

LineItemID   Order ID    Quantity   Description
==========   ========    ========   =================================
{A58A1...}   6,784,329   5          pentametric fan
{0E9BC...}   6,784,329   5          differential girdlespring

通常在向用户显示订单时：

SELECT Orders.OrderNumber, LineItems.Quantity, LineItems.Description
FROM Orders
    INNER JOIN LineItems 
    ON Orders.OrderID = LineItems.OrderID

我想在订单上显示单个项目。但是，如果这个偶然的订单包含两个（或多个）项目，那么这些订单将看起来是重复的：

OrderNumber   Quantity   Description
===========   ========   ====================
STL-7442-1    7          prefabulated amulite
MPT-9931-8A   32         spurving bearing
KSG-0619-81   5          panametric fan
KSG-0619-81   5          differential girdlespring

我真正想要的是让 SQL Server仅选择一个，因为这样就足够了：

OrderNumber   Quantity   Description
===========   ========   ====================
STL-7442-1    7          prefabulated amulite
MPT-9931-8A   32         differential girdlespring
KSG-0619-81   5          panametric fan

如果我喜欢冒险，可以向用户显示一个省略号，以表明有多个：

OrderNumber   Quantity   Description
===========   ========   ====================
STL-7442-1    7          prefabulated amulite
MPT-9931-8A   32         differential girdlespring
KSG-0619-81   5          panametric fan, ...

所以问题是如何

• 消除 “重复” 行
• 仅连接到其中一行，以避免重复

第一次尝试

我的首次尝试是仅加入 “ TOP 1 ” 行项目：

SELECT Orders.OrderNumber, LineItems.Quantity, LineItems.Description
FROM Orders
    INNER JOIN (
       SELECT TOP 1 LineItems.Quantity, LineItems.Description
       FROM LineItems
       WHERE LineItems.OrderID = Orders.OrderID) LineItems2
    ON 1=1

但这给出了错误：

列或前缀 “订单” 不
与表名或别名匹配
在查询中使用。

大概是因为内部选择看不到外部表。

SELECT   Orders.OrderNumber, LineItems.Quantity, LineItems.Description
FROM     Orders
JOIN     LineItems
ON       LineItems.LineItemGUID =
         (
         SELECT  TOP 1 LineItemGUID 
         FROM    LineItems
         WHERE   OrderID = Orders.OrderID
         )

在 SQL Server 2005 及更高版本中，您可以将INNER JOIN替换为CROSS APPLY ：

SELECT  Orders.OrderNumber, LineItems2.Quantity, LineItems2.Description
FROM    Orders
CROSS APPLY
        (
        SELECT  TOP 1 LineItems.Quantity, LineItems.Description
        FROM    LineItems
        WHERE   LineItems.OrderID = Orders.OrderID
        ) LineItems2

请注意，没有ORDER BY TOP 1不是确定性的：此查询为每个订单提供一个订单项，但未定义为哪个订单项。

即使基础没有变化，多次调用查询也可以为同一订单提供不同的订单项。

如果要确定顺序，则应在最里面的查询中ORDER BY

sqlfiddle 示例

我知道这个问题是在不久前回答的，但是当处理大型数据集时，嵌套查询的成本可能很高。这是另一种解决方案，其中嵌套查询将只运行一次，而不是为返回的每一行运行一次。

SELECT 
  Orders.OrderNumber,
  LineItems.Quantity, 
  LineItems.Description
FROM 
  Orders
  INNER JOIN (
    SELECT
      Orders.OrderNumber,
      Max(LineItem.LineItemID) AS LineItemID
    FROM
      Orders INNER JOIN LineItems
      ON Orders.OrderNumber = LineItems.OrderNumber
    GROUP BY Orders.OrderNumber
  ) AS Items ON Orders.OrderNumber = Items.OrderNumber
  INNER JOIN LineItems 
  ON Items.LineItemID = LineItems.LineItemID

@Quassnoi 的答案很好，在某些情况下（尤其是外部表很大的情况下），使用窗口函数可以实现更有效的查询，如下所示：

SELECT  Orders.OrderNumber, LineItems2.Quantity, LineItems2.Description
FROM    Orders
LEFT JOIN 
        (
        SELECT  LineItems.Quantity, LineItems.Description, OrderId, ROW_NUMBER()
                OVER (PARTITION BY OrderId ORDER BY (SELECT NULL)) AS RowNum
        FROM    LineItems

        ) LineItems2 ON LineItems2.OrderId = Orders.OrderID And RowNum = 1

有时，您只需要测试哪个查询可以提供更好的性能。