Verbinden mit der ersten Reihe

Question

Ich möchte ein konkretes, aber hypothetisches Beispiel anführen.

Jede Bestellung hat normalerweise nur einen Einzelposten :

Bestellungen:

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

Einzelposten:

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

Gelegentlich kommt es jedoch vor, dass ein Auftrag zwei Positionen enthält:

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

Normalerweise, wenn die Bestellungen dem Benutzer angezeigt werden:

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

Ich möchte den einzelnen Artikel auf der Bestellung anzeigen. Aber mit dieser gelegentlichen Bestellung, die zwei (oder mehr) Artikel enthält, würden die Bestellungen erscheinen sein vervielfältigt :

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

Was ich wirklich möchte, ist, dass SQL Server Wählen Sie einfach einen aus wie es sein wird gut genug :

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

Wenn ich abenteuerlustig bin, könnte ich dem Benutzer eine Ellipse zeigen, um anzuzeigen, dass es mehr als eine gibt:

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

Die Frage ist also, wie man entweder

• Beseitigung "doppelter" Zeilen
• nur mit einer der Zeilen verknüpfen, um Doppelungen zu vermeiden

Erster Versuch

Mein erster naiver Versuch bestand darin, sich nur mit dem " TOP 1 Einzelposten":

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

Aber das führt zu dem Fehler:

Die Spalte oder das Präfix 'Orders' bedeutet nicht
Übereinstimmung mit einem Tabellennamen oder einem Aliasnamen
in der Abfrage verwendet.

Vermutlich, weil der innere Select die äußere Tabelle nicht sieht.

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 und höher können Sie einfach die INNER JOIN avec 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

Bitte beachten Sie, dass TOP 1 ohne ORDER BY ist nicht deterministisch: Mit dieser Abfrage erhalten Sie eine Position pro Auftrag, aber es ist nicht definiert, welche das sein wird.

Mehrere Aufrufe der Abfrage können unterschiedliche Positionen für denselben Auftrag ergeben, auch wenn sich der zugrunde liegende Wert nicht geändert hat.

Wenn Sie eine deterministische Reihenfolge wünschen, sollten Sie eine ORDER BY Klausel an die innerste Abfrage an.

Beispiel sqlfiddle

Answer 2

Ich weiß, dass diese Frage schon vor einiger Zeit beantwortet wurde, aber bei großen Datensätzen können verschachtelte Abfragen kostspielig sein. Hier ist eine andere Lösung, bei der die verschachtelte Abfrage nur einmal ausgeführt wird, anstatt für jede zurückgegebene Zeile.

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

Answer 3

@Quassnoi Antwort ist gut, in einigen Fällen (vor allem, wenn die äußere Tabelle groß ist), könnte eine effizientere Abfrage mit gefensterten Funktionen, wie diese sein:

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

Manchmal muss man einfach Notwendigkeit der Prüfung welche Abfrage eine bessere Leistung bringt.

Answer 4

Das könnten Sie tun:

SELECT 
  Orders.OrderNumber, 
  LineItems.Quantity, 
  LineItems.Description
FROM 
  Orders INNER JOIN LineItems 
  ON Orders.OrderID = LineItems.OrderID
WHERE
  LineItems.LineItemID = (
    SELECT MIN(LineItemID) 
    FROM   LineItems
    WHERE  OrderID = Orders.OrderID
  )

Dies erfordert einen Index (oder Primärschlüssel) auf LineItems.LineItemID und einen Index auf LineItems.OrderID oder es wird langsam sein.

Answer 5

Ab SQL Server 2012 denke ich, dass dies der richtige Weg ist:

SELECT DISTINCT
    o.OrderNumber ,
    FIRST_VALUE(li.Quantity) OVER ( PARTITION BY o.OrderNumber ORDER BY li.Description ) AS Quantity ,
    FIRST_VALUE(li.Description) OVER ( PARTITION BY o.OrderNumber ORDER BY li.Description ) AS Description
FROM    Orders AS o
    INNER JOIN LineItems AS li ON o.OrderID = li.OrderID