如何加入到第一行 [英] How to Join to first row

问题描述

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

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.

大概是因为内部选择没有看到外部表.

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

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