Jak dołączyć do pierwszego rzędu


773

Wykorzystam konkretny, ale hipotetyczny przykład.

Każde zamówienie ma zwykle tylko jeden element zamówienia :

Zamówienia:

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

Ale czasami będzie zamówienie z dwoma elementami:

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

Zwykle podczas wyświetlania zamówień użytkownikowi:

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

Chcę pokazać pojedynczy element na zamówieniu. Ale z tego okazjonalne zamówienie zawierające dwa (lub więcej) elementów, zamówienia będą pojawiać być powielany :

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

Naprawdę chcę, aby SQL Server wybrał jeden , ponieważ będzie wystarczająco dobry :

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

Jeśli zacznę przygodę, mogę pokazać użytkownikowi elipsę, która wskazuje, że jest więcej niż jeden:

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

Pytanie brzmi: jak to zrobić?

  • wyeliminuj „zduplikowane” wiersze
  • dołącz tylko do jednego z wierszy, aby uniknąć powielania

Pierwsze podejscie

Moja pierwsza naiwna próba polegała na dołączeniu tylko do elementów zamówienia „ TOP 1 ”:

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

Ale to daje błąd:

Kolumna lub przedrostek „Zamówienia” nie są
zgodne z nazwą tabeli lub nazwą aliasu
użytą w zapytaniu.

Prawdopodobnie dlatego, że wewnętrzny wybór nie widzi tabeli zewnętrznej.


3
Nie można używać group by?
Dariush Jafari,

2
Myślę, że (i popraw mnie, jeśli się mylę) group bywymagałoby wyszczególnienia wszystkich pozostałych kolumn, z wyjątkiem tej, w której nie chcesz duplikatów. Źródło
Joshua Nelson

Odpowiedzi:


1212
SELECT   Orders.OrderNumber, LineItems.Quantity, LineItems.Description
FROM     Orders
JOIN     LineItems
ON       LineItems.LineItemGUID =
         (
         SELECT  TOP 1 LineItemGUID 
         FROM    LineItems
         WHERE   OrderID = Orders.OrderID
         )

W SQL Server 2005 i powyżej, można po prostu zastąpić INNER JOINz 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

Pamiętaj, że „ TOP 1bez” ORDER BYnie jest deterministyczne: w tym zapytaniu otrzymasz jeden element zamówienia na zamówienie, ale nie jest określone, który z nich będzie.

Wiele wywołań zapytania może dać różne elementy zamówienia dla tego samego zamówienia, nawet jeśli instrument bazowy nie zmienił się.

Jeśli chcesz uporządkować deterministycznie, dodaj ORDER BYklauzulę do najbardziej wewnętrznego zapytania.


3
Doskonale, to działa; przeniesienie TOP 1 z pochodnej klauzuli tabeli do klauzuli łączenia.
Ian Boyd,

107
a odpowiednikiem „OUTER JOIN” będzie „OUTER APPLY”
Alex

9
Co powiesz na POŁĄCZENIE ZEWNĘTRZNE?
Alex Nolasco,

8
Jak to zrobić, jeśli łączenie odbywa się za pomocą klucza złożonego / ma wiele kolumn?
Brett Ryan,

7
CROSS APPLYzamiast INNER JOINi OUTER APPLYzamiast LEFT JOIN(to samo co LEFT OUTER JOIN).
hastrb

117

Wiem, że odpowiedziano na to pytanie jakiś czas temu, ale w przypadku dużych zestawów danych zapytania zagnieżdżone mogą być kosztowne. Oto inne rozwiązanie, w którym zagnieżdżone zapytanie będzie uruchamiane tylko raz, zamiast dla każdego zwracanego wiersza.

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

2
Jest to również znacznie szybciej, jeśli twój „LineItemId” kolumna nie jest poprawnie indeksowana. W porównaniu do zaakceptowanej odpowiedzi.
GER,

3
Ale jak byś to zrobił, gdyby Max nie był użyteczny, ponieważ musisz zamówić według kolumny innej niż ta, którą chcesz zwrócić?
NickG

2
możesz zamówić tabelę pochodną w dowolny sposób i użyć TOP 1 w SQL Server lub LIMIT 1 w MySQL
stifin

28

Mógłbyś:

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
  )

Wymaga to włączenia indeksu (lub klucza podstawowego) LineItems.LineItemIDi włączenia indeksu LineItems.OrderIDlub będzie on powolny.


2
To nie działa, jeśli zamówienie nie ma LineItems. Podwyrażenie następnie ocenia LineItems.LineItemID = nulli całkowicie usuwa zamówienia lewego bytu z wyniku.
leo

6
To także efekt połączenia wewnętrznego, więc ... tak.
Tomalak,

1
Rozwiązanie, które można dostosować do POŁĄCZENIA ZEWNĘTRZNEGO: stackoverflow.com/a/20576200/510583
Leo

3
@leo Tak, ale OP sam użył wewnętrznego połączenia, więc nie rozumiem twojego sprzeciwu.
Tomalak,

27

Odpowiedź @Quassnoi jest dobra, w niektórych przypadkach (szczególnie jeśli tabela zewnętrzna jest duża), bardziej wydajne zapytanie może być przy użyciu funkcji okienkowych, takich jak to:

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

Czasami wystarczy przetestować, które zapytanie daje lepszą wydajność.


3
To jedyna odpowiedź, jaką znalazłem, która wykonuje prawdziwe „lewe” połączenie, co oznacza, że ​​nie dodaje już żadnych wierszy, niż znajduje się w tabeli „lewej”. Musisz tylko wpisać podzapytanie i dodać „gdzie RowNum nie jest pusty”
user890332

1
Zgodził się, że to najlepsze rozwiązanie. To rozwiązanie nie wymaga również posiadania unikalnego identyfikatora w tabeli, do której dołączasz, i jest znacznie szybsze niż najlepiej głosowana odpowiedź. Możesz również dodać kryteria, dla których wiersz chcesz zwrócić, zamiast brać losowy wiersz, używając klauzuli ORDER BY w podzapytaniu.
Geoff Griswald

To dobre rozwiązanie. Uwaga: w przypadku korzystania z własnej sytuacji należy bardzo uważać na PARTYCJĘ PRZEZ (zwykle tam jest pewna kolumna identyfikacyjna) i ORDER BY (co można zrobić w większości przypadków, w zależności od tego, który wiersz chcemy zachować, np. DateCreated desc będzie jednym wyborem dla niektórych stolików, ale będzie zależeć od wielu rzeczy)
JosephDoggie

14

, Kolejne podejście przy użyciu wspólnego wyrażenia tabelowego:

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

a może na koniec chciałbyś pokazać wszystkie połączone rzędy?

Wersja oddzielona przecinkami tutaj:

  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

13

Od SQL Server 2012 i później myślę, że to załatwi sprawę:

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

2
Najlepsza odpowiedź, jeśli mnie zapytasz.
Thomas

11

Skorelowane podzapytania to podzapytania zależne od zapytania zewnętrznego. To jest jak pętla for w SQL. Zapytanie podrzędne będzie uruchamiane raz dla każdego wiersza w zapytaniu zewnętrznym:

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
)

5

EDYCJA: nieważne, Quassnoi ma lepszą odpowiedź.

W przypadku SQL2K coś takiego:

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

4

Moim ulubionym sposobem uruchomienia tego zapytania jest klauzula nieistniejąca. Uważam, że jest to najbardziej skuteczny sposób na uruchomienie tego rodzaju zapytania:

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
    )

Ale nie przetestowałem tej metody w stosunku do innych sugerowanych tutaj metod.


2

Próbowałem krzyża, działa ładnie, ale trwa nieco dłużej. Skorygowano kolumny linii, aby mieć maksymalną i dodaną grupę, która utrzymywała prędkość i usunęła dodatkowy rekord.

Oto dostosowane zapytanie:

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

10
Ale posiadanie maksimum oddzielnie w dwóch kolumnach oznacza, że ​​ilość może nie być związana z opisem. Gdyby zamówienie obejmowało 2 widżety i 10 gadżetów, zapytanie zwróciłoby 10 widżetów.
Brianorca

1

Spróbuj tego

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

2
Proszę rozważyć wyjaśnienie, co robi zapytanie, aby rozwiązać problem PO
Simas Joneliunas,
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.