sql - SQL Server：如何加入第一行(SQL Server: How to Join to first row)

Question

I'll use a concrete, but hypothetical, example.

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

Each Order normally has only one line item :

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

Orders:

(命令：)

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

LineItems:

(LineItems：)

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

But occasionally there will be an order with two line items:

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

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

Normally when showing the orders to the user:

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

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

I want to show the single item on the order.

(我想在订单上显示单个项目。)

But with this occasional order containing two (or more) items, the orders would appear be duplicated :

(但是，如果这个偶然的订单包含两个（或多个）项目，这些订单将看起来是重复的 ：)

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

What I really want is to have SQL Server just pick one , as it will be good enough :

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

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

If I get adventurous, I might show the user, an ellipsis to indicate that there's more than one:

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

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

So the question is how to either

(所以问题是如何)

• eliminate "duplicate" rows

  (消除“重复”行)

• only join to one of the rows, to avoid duplication

  (仅连接到其中一行，以避免重复)

First attempt (第一次尝试)

My first naive attempt was to only join to the " TOP 1 " line items:

(我的第一个天真的尝试是仅加入“ 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

But that gives the error:

(但这给出了错误：)

The column or prefix 'Orders' does not

(列或前缀“订单”不)
match with a table name or alias name

(与表名或别名匹配)
used in the query.

(在查询中使用。)

Presumably because the inner select doesn't see the outer table.

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

  ask by Ian Boyd translate from so

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
         )

In SQL Server 2005 and above, you could just replace INNER JOIN with CROSS APPLY :

(在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

Please note that TOP 1 without ORDER BY is not deterministic: this query you will get you one line item per order, but it is not defined which one will it be.

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

Multiple invocations of the query can give you different line items for the same order, even if the underlying did not change.

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

If you want deterministic order, you should add an ORDER BY clause to the innermost query.

(如果要确定顺序，则应在最里面的查询中添加ORDER BY子句。)