sql - 如何连接到第一行

Question

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

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

订单:

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

订单项:

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

但这给出了错误:

The column or prefix 'Orders' does not
match with a table name or alias name
used in the query.

可能是因为内部选择看不到外部表。

Answer 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 子句。

Example sqlfiddle