zeilen SQL Server: So verbinden Sie sich mit der ersten Zeile




sql unterabfrage für eine zeile liefert mehr als eine zeile (8)

Ich weiß, dass diese Frage vor einer Weile beantwortet wurde, aber bei großen Datenmengen können verschachtelte Abfragen teuer sein. Hier ist eine andere Lösung, bei der die verschachtelte Abfrage nur einmal statt für jede zurückgegebene Zeile ausgeführt wird.

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

Ich werde ein konkretes, aber hypothetisches Beispiel verwenden.

Jeder Auftrag hat normalerweise nur eine Werbebuchung :

Aufträge:

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

LineItems:

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

Gelegentlich gibt es jedoch eine Bestellung mit zwei Werbebuchungen:

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 in der Bestellung anzeigen. Aber mit dieser gelegentlichen Bestellung, die zwei (oder mehr) Artikel enthält, erscheinen die Bestellungen doppelt :

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 nur einen auswählt , weil es gut genug ist :

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

Wenn ich abenteuerlustig werde, zeige ich dem Nutzer vielleicht eine Ellipse, 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

  • Eliminiere "doppelte" Zeilen
  • verbinden Sie nur mit einer der Zeilen, um Doppelungen zu vermeiden

Erster Versuch

Mein erster naive Versuch war, mich nur den Werbebuchungen " TOP 1 " anzuschließen:

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 gibt den Fehler:

Die Spalte oder das Präfix "Bestellungen" stimmt nicht mit einem Tabellennamen oder Aliasnamen überein, der in der Abfrage verwendet wird.

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


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 INNER JOIN einfach durch CROSS APPLY ersetzen:

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

Ich löse ein ähnliches Problem, indem ich LEFT JOIN und GROUP BY Orders.OrderNumber verwende. Gibt es einen Grund, es nicht so zu machen?

SELECT Orders.OrderNumber, LineItems.Quantity, LineItems.Description
FROM Orders
    LEFT JOIN LineItems 
    ON Orders.OrderID = LineItems.OrderID
GROUP BY Orders.OrderNumber

Ich beantworte Ihre Antwort mit einer Antwort auf Ihre Frage:

Orders             LineItems
+-------------+    +---------+----------+---------------+
| OrderNumber |    | OrderID | Quantity | Description   |
+-------------+    +---------+----------+---------------+
| 22586       |    | 22586   | 17       | Trunion       |
+-------------+    | 22586   | 3        | Girdle Spring |
                   +---------+----------+---------------+

Wenn Sie die beiden bei OrderNumber zusammenfügen, erhalten Sie:

OrderNumber  Quantity  Description
-----------  --------  -------------
22586        17        Trunion
22586        3         Girdle Spring

2 row(s) affected

Wo wir es wollten, um nur eine Zeile zurückzugeben:

OrderNumber  Quantity  Description
-----------  --------  -------------
22586        17        Trunion

1 row(s) affected

Deshalb verwende ich GROUP BY Orders.OrderNumber, das nur eine Zeile pro OrderNumber zurückgibt.


Du könntest es 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) für LineItems.LineItemID und einen Index für LineItems.OrderID oder er wird langsam sein.


EDIT: vergiss, Quassnoi hat eine bessere Antwort.

Für SQL2K, etwas wie folgt:

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

, Eine weitere Vorgehensweise, die den allgemeinen Tabellenausdruck verwendet:

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 Reihen zeigen?

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

Meine bevorzugte Methode zum Ausführen dieser Abfrage ist eine nicht exists-Klausel. Ich glaube, dies ist der effizienteste Weg, 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
    )

Aber ich habe diese Methode nicht gegen andere hier vorgeschlagene Methoden getestet.


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
)




sql-server-2000