问题描述

我将使用一个具体但假设的例子.

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

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

订单:

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

但偶尔会有两个订单项的订单:

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

我真正想要的是让 SQL Server随便挑一个，因为它会足够好:

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

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, ...

所以问题是如何选择

• 消除重复"行
• 只加入其中一行，以避免重复

我第一次天真的尝试是只加入TOP 1"订单项:

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

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.

大概是因为inner select没有看到outer table.

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

推荐答案

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:

In SQL Server 2005 and above, you could just replace INNER JOIN with 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 不是确定性的:此查询您将为每个订单获取一个订单项，但未定义它将获取哪个订单项是.

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.

查询的多次调用可以为同一订单提供不同的订单项，即使底层证券没有改变.

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

如果你想要确定的顺序，你应该在最里面的查询中添加一个 ORDER BY 子句.

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

sqlfiddle 示例

这篇关于如何加入第一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！