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

Ein anderer Ansatz ist die Verwendung eines gemeinsamen Tabellenausdrucks:

with firstOnly as (
    select Orders.OrderNumber, LineItems.Quantity, LineItems.Description, ROW_NUMBER() over (partiton by Orders.OrderID order by Orders.OrderID) lp
    FROM Orders
        join LineItems on Orders.OrderID = LineItems.OrderID
) select *
  from firstOnly
  where lp = 1

oder möchten Sie am Ende vielleicht alle verbundenen Zeilen anzeigen?

Komma getrennte Version hier:

  select *
  from Orders o
    cross apply (
        select CAST((select l.Description + ','
        from LineItems l
        where l.OrderID = s.OrderID
        for xml path('')) as nvarchar(max)) l
    ) lines

Answer 2

Korrelierte Unterabfragen sind Unterabfragen, die von der äußeren Abfrage abhängen. Es ist wie eine for-Schleife in SQL. Die Unterabfrage wird für jede Zeile in der äußeren Abfrage einmal ausgeführt:

select * from users join widgets on widgets.id = (
    select id from widgets
    where widgets.user_id = users.id
    order by created_at desc
    limit 1
)

Answer 3

Am liebsten führe ich diese Abfrage mit einer not exists-Klausel aus. Ich glaube, dass dies der effizienteste Weg ist, um diese Art von Abfrage auszuführen:

select o.OrderNumber,
       li.Quantity,
       li.Description
from Orders as o
inner join LineItems as li
on li.OrderID = o.OrderID
where not exists (
    select 1
    from LineItems as li_later
    where li_later.OrderID = o.OrderID
    and li_later.LineItemGUID > li.LineItemGUID
    )

Ich habe diese Methode jedoch nicht mit anderen hier vorgeschlagenen Methoden verglichen.

Answer 4

EDIT: Vergessen Sie es, Quassnoi hat eine bessere Antwort.

Für SQL2K, etwa so:

SELECT 
  Orders.OrderNumber
, LineItems.Quantity
, LineItems.Description
FROM (  
  SELECT 
    Orders.OrderID
  , Orders.OrderNumber
  , FirstLineItemID = (
      SELECT TOP 1 LineItemID
      FROM LineItems
      WHERE LineItems.OrderID = Orders.OrderID
      ORDER BY LineItemID -- or whatever else
      )
  FROM Orders
  ) Orders
JOIN LineItems 
  ON LineItems.OrderID = Orders.OrderID 
 AND LineItems.LineItemID = Orders.FirstLineItemID

Answer 5

Ich habe das Kreuz ausprobiert, es funktioniert gut, dauert aber etwas länger. Angepasste Zeilenspalten zu haben, max und fügte Gruppe, die Geschwindigkeit gehalten und ließ den zusätzlichen Datensatz.

Hier ist die angepasste Abfrage:

SELECT Orders.OrderNumber, max(LineItems.Quantity), max(LineItems.Description)
FROM Orders
    INNER JOIN LineItems 
    ON Orders.OrderID = LineItems.OrderID
Group by Orders.OrderNumber