SQL Server: Jak dołączyć do pierwszego wiersza
Użyję konkretnego, ale hipotetycznego przykładu.
Każdy rozkaz zwykle ma tylko jeden element linii:
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 pozycjami liniowymi:
LineItemID Order ID Quantity Description
========== ======== ======== =================================
{A58A1...} 6,784,329 5 pentametric fan
{0E9BC...} 6,784,329 5 differential girdlespring
Normalnie przy pokazywaniu zleceń użytkownikowi:
SELECT Orders.OrderNumber, LineItems.Quantity, LineItems.Description
FROM Orders
INNER JOIN LineItems
ON Orders.OrderID = LineItems.OrderID
Chcę pokazać pojedynczy przedmiot w zamówieniu. Ale przy tym okazjonalnym zamówieniu zawierającym dwa (lub więcej) przedmioty, zamówienia będą appeared 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
Naprawdę chcę mieć SQL Server po prostu wybierz 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 dostanę awantury, mogę pokazać użytkownikowi, elipsę wskazującą, że jest więcej niż jedna:
OrderNumber Quantity Description
=========== ======== ====================
STL-7442-1 7 prefabulated amulite
MPT-9931-8A 32 differential girdlespring
KSG-0619-81 5 panametric fan, ...
Więc pytanie brzmi, jak albo
- wyeliminuj" duplikaty " wierszy
- Dołącz tylko do jednego z wierszy, aby uniknąć powielania
Pierwsza próba
Mój pierwsza naiwna próba polegała na dołączeniu tylko do pozycji" 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 Dopasuj do nazwy tabeli lub nazwy aliasu użyte w zapytaniu.
Prawdopodobnie dlatego, że wewnętrzny select nie widzi zewnętrznej tabeli.
10 answers
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 wyżej, możesz zastąpić INNER JOIN
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
Proszę zauważyć, że TOP 1
bez ORDER BY
nie jest deterministyczne: to zapytanie otrzymasz jeden element liniowy na zamówienie, ale nie jest zdefiniowane, który z nich będzie.
Wielokrotne wywołania zapytania mogą dać różne pozycje linii dla tej samej kolejności, nawet jeśli podstawa nie uległa zmianie.
Jeśli chcesz deterministycznego porządku, powinieneś dodać klauzulę ORDER BY
do najbardziej wewnętrznego zapytania.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-09-21 11:20:38
Wiem, że odpowiedź na to pytanie została udzielona jakiś czas temu, ale w przypadku dużych zbiorów danych zagnieżdżone zapytania mogą być kosztowne. Oto inne rozwiązanie, w którym zagnieżdżone zapytanie zostanie uruchomione 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-04-06 22:34:38
Możesz zrobić:
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
)
To wymaga indeksu (lub klucza głównego) na LineItems.LineItemID
i indeksu na LineItems.OrderID
, w przeciwnym razie będzie to powolne.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-01-11 16:50:24
@ Quassnoi odpowiedź jest dobra, w niektórych przypadkach (szczególnie jeśli zewnętrzna tabela jest duża), bardziej wydajne zapytanie może być za pomocą funkcji okienkowych, takich jak:
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 po prostu musisz przetestować które zapytanie daje lepszą wydajność.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-04-13 12:42:38
Skorelowane zapytania podrzędne To zapytania podrzędne, które zależą od zapytania zewnętrznego. To jak pętla for w SQL. Zapytanie podrzędne uruchomi się 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
)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-09-17 10:38:08
, Inny aproach używający wyrażenia common table:
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 wiersze?
Oddzielona przecinkami wersja 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-10 10:18:11
EDIT: nevermind, Quassnoi ma lepszą odpowiedź.
Dla 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-01-11 17:23:56
Rozwiązuję podobny problem używając LEFT JOIN i GROUP BY Orders.Numer porządkowy. Czy jest jakiś powód, żeby tego nie robić w ten sposób?
SELECT Orders.OrderNumber, LineItems.Quantity, LineItems.Description
FROM Orders
LEFT JOIN LineItems
ON Orders.OrderID = LineItems.OrderID
GROUP BY Orders.OrderNumber
Odpowiem na twoje pytanie z odpowiedzią na twoje własne pytanie:
Orders LineItems
+-------------+ +---------+----------+---------------+
| OrderNumber | | OrderID | Quantity | Description |
+-------------+ +---------+----------+---------------+
| 22586 | | 22586 | 17 | Trunion |
+-------------+ | 22586 | 3 | Girdle Spring |
+---------+----------+---------------+
Połączenie tych dwóch razem na OrderNumber daje:
OrderNumber Quantity Description
----------- -------- -------------
22586 17 Trunion
22586 3 Girdle Spring
2 row(s) affected
Gdzie chcieliśmy, aby zwrócił tylko jeden wiersz:
OrderNumber Quantity Description
----------- -------- -------------
22586 17 Trunion
1 row(s) affected
Dlatego używam rozkazów GROUP BY.OrderNumber, który zwraca tylko jeden wiersz na OrderNumber.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-09-14 07:38:46
Moim ulubionym sposobem uruchamiania tego zapytania jest użycie klauzuli not exists. Uważam, że jest to najskuteczniejszy sposób uruchamiania tego typu zapytań:
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 testowałem tej metody na tle innych metod sugerowanych tutaj.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-09 18:12:59
Wypróbowałem Krzyż, działa ładnie, ale trwa nieco dłużej. Dostosowano kolumny linii, aby miały max i dodano grupę, która utrzymywała prędkość i upuszczała dodatkowy rekord.
Oto dopasowane zapytanie:
SELECT Orders.OrderNumber, max(LineItems.Quantity), max(LineItems.Description)
FROM Orders
INNER JOIN LineItems
ON Orders.OrderID = LineItems.OrderID
Group by Orders.OrderNumber
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-02-14 22:07:30