Как присоединиться к первому ряду

avatar
Ian Boyd
11 января 2010 в 16:44
659647
11
837

Я буду использовать конкретный, но гипотетический пример.

Каждый заказ обычно имеет только одну позицию :

Заказы :

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

Но иногда бывает заказ с двумя позициями:

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

Обычно при отображении заказов пользователю:

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

Я хочу показать отдельный элемент в заказе. Но с этим случайным заказом, содержащим два (или более) элемента, заказы будут казаться дублированными :

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

Я действительно хочу, чтобы SQL Server просто выбрал один , так как он будет достаточно хорошим :

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

Если я буду любить приключения, я могу показать пользователю многоточие, чтобы указать, что их несколько:

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

Итак, вопрос в том, как

  • удалить "повторяющиеся" строки
  • присоединиться только к одной из строк, чтобы избежать дублирования

Первая попытка

Моя первая наивная попытка заключалась в том, чтобы присоединиться только к позициям « 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

Но это дает ошибку:

Столбец или префикс "Заказы" не
совпадение с именем таблицы или псевдонимом
используется в запросе.

Предположительно потому, что внутренний выбор не видит внешнюю таблицу.

Источник
Dariush Jafari
11 апреля 2017 в 07:54
3

Разве вы не можете использовать group by?

Joshua Nelson
1 июня 2018 в 12:25
2

Я думаю (и поправьте меня, если я ошибаюсь) group by потребует перечисления всех других столбцов, за исключением того, где вы не хотите дублировать. Источник

Ответы (11)

avatar
Quassnoi
11 января 2010 в 16:48
1331
SELECT   Orders.OrderNumber, LineItems.Quantity, LineItems.Description
FROM     Orders
JOIN     LineItems
ON       LineItems.LineItemGUID =
         (
         SELECT  TOP 1 LineItemGUID 
         FROM    LineItems
         WHERE   OrderID = Orders.OrderID
         )

В SQL Server 2005 и более поздних версиях вы можете просто заменить 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

Обратите внимание, что TOP 1 без ORDER BY не является детерминированным: в этом запросе вы получите одну позицию на заказ, но не определено, какая именно.

Несколько вызовов запроса могут дать вам разные позиции для одного и того же заказа, даже если базовый не изменился.

Если вам нужен детерминированный порядок, вы должны добавить предложение ORDER BY к самому внутреннему запросу.

Пример sqlfiddle

Ian Boyd
11 января 2010 в 16:54
3

Отлично, это работает; перемещение TOP 1 из предложения производной таблицы в предложение соединения.

Alex from Jitbit
22 декабря 2011 в 10:41
120

и эквивалент "OUTER JOIN" будет "OUTER APPLY"

Alex Nolasco
31 января 2012 в 23:11
9

Как насчет LEFT OUTER JOIN?

Quassnoi
1 февраля 2012 в 09:53
0

@AlexanderN: что именно вы хотите знать о LEFT OUTER JOIN?

Brett Ryan
12 сентября 2012 в 07:20
8

Как это сделать, если соединение выполняется с помощью составного ключа / имеет несколько столбцов?

Sunday Ironfoot
11 декабря 2012 в 11:19
0

Благодаря этому я просто сократил время выполнения сложного запроса T-SQL на 1,3 секунды (с 3,7 до 2,4 секунды). :-D

Quassnoi
29 ноября 2013 в 18:25
0

@Lex: INNER JOIN не генерирует значение NULL для строк, отсутствующих в объединенном запросе, а LEFT JOIN делает.

Brett Ryan
26 июня 2014 в 03:50
0

Есть ли способ сделать это без top 1 во внутреннем выборе? К сожалению, OpenEdge настолько современен, что вы получаете [DataDirect][OpenEdge JDBC Driver][OpenEdge] TOP clause used in unsupported context. (13694)

Jeff Davis
17 сентября 2015 в 14:27
1

Есть ли преимущества в использовании Cross Apply?

Quassnoi
17 сентября 2015 в 14:31
2

@JeffDavis: это сэкономит вам один поиск на строку, если OrderId является ведущим столбцом в первичном ключе.

BornToCode
6 декабря 2015 в 11:28
0

@Quassnoi - Не могли бы вы объяснить свой последний комментарий о том, что перекрестное применение более эффективно, если идентификатор заказа является первичным ключом? Почему это так и почему этого не происходит с соединением?

Quassnoi
6 декабря 2015 в 12:30
1

@BornToCode: cross apply будет извлекать всю запись и сразу же возвращать ее. Join извлечет строку, возьмет из нее guid позиции, сам присоединится к таблице по guid (это дополнительный поиск) и вернет запись из присоединенной таблицы.

Dirk Horsten
7 апреля 2016 в 08:16
0

К сожалению, не поддерживается в Sybase IQ

statler
16 января 2017 в 10:07
1

Важно отметить, что второе решение - Cross Apply / Outer Apply будет работать с SQLCE, который не может использовать скалярные подзапросы в соединениях согласно верхнему запросу.

hastrb
15 марта 2018 в 12:04
10

CROSS APPLY вместо INNER JOIN и OUTER APPLY вместо LEFT JOIN (то же, что и LEFT OUTER JOIN).

Paul Wehland
21 сентября 2018 в 10:59
2

Имейте в виду, что TOP без ORDER BY недетерминирован. Вы не гарантируете получение той строки, на которую рассчитываете.

9Rune5
6 декабря 2018 в 13:05
2

В качестве альтернативы используйте MIN () вместо TOP 1. Я обнаружил, что обычно меня интересует первый Id.

user274294
24 июля 2019 в 08:27
0

CROSS APPLY (в большинстве случаев) займет много времени ... Вместо этого пользовательское OUTER APPLY.

Andrew
7 июля 2020 в 16:00
1

🤯 КРЕСТНОЕ ПРИМЕНЕНИЕ. Спасибо!!

Abdellah GRIB
25 января 2021 в 10:52
0

Это не сработает, если PK также является FK, который ссылается на родительскую таблицу. т.е. если один и тот же столбец используется для "on" и с "where"! Например. : from Parent pt inner join Child cd on cd.ChildId = ( select top 1 ChildId from Child where ChildId = pt.ChildId )

Quassnoi
25 января 2021 в 11:43
0

@AbdellahGrib: вопрос был о том, как избавиться от дубликатов. Первичный ключ не может иметь дубликатов.

avatar
Bane Neba
19 февраля 2020 в 05:42
2

попробуйте это

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
Simas Joneliunas
19 февраля 2020 в 06:01
4

Пожалуйста, подумайте о том, чтобы объяснить, что делает ваш запрос для решения проблемы OP

avatar
P. Olesen
28 ноября 2018 в 11:55
19

Начиная с SQL Server 2012, я думаю, это поможет:

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
thomas
28 ноября 2019 в 23:45
4

Лучший ответ, если вы спросите меня.

Hoang Tran
23 февраля 2021 в 07:51
0

Я думаю это лучший ответ

avatar
avb
10 мая 2017 в 10:01
15

, Другой подход с использованием общего табличного выражения:

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

или, в конце концов, может быть, вы хотите показать все соединенные строки?

версия, разделенная запятыми:

  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
avatar
Anand
9 мая 2017 в 18:12
4

Я предпочитаю выполнять этот запрос с условием "не существует". Я считаю, что это наиболее эффективный способ выполнить такой запрос:

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
    )

Но я не тестировал этот метод по сравнению с другими методами, предлагаемыми здесь.

avatar
Abdullah Yousuf
17 сентября 2016 в 10:19
11

Коррелированные подзапросы - это подзапросы, которые зависят от внешнего запроса. Это похоже на цикл for в SQL. Подзапрос будет выполняться один раз для каждой строки внешнего запроса:

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
)
avatar
BornToCode
3 марта 2016 в 16:14
35

Ответ @Quassnoi хороший, в некоторых случаях (особенно если внешняя таблица большая) более эффективный запрос может быть с использованием оконных функций, например:

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

Иногда вам просто нужно проверить, какой запрос дает лучшую производительность.

user890332
10 мая 2019 в 17:55
3

Это единственный найденный мной ответ, который выполняет настоящее «левое» соединение, то есть не добавляет больше строк, чем находится в «левой» таблице. Вам просто нужно ввести подзапрос и добавить «где RowNum не равно нулю»

Geoff Griswald
3 октября 2019 в 14:06
1

Согласен, это лучшее решение. Это решение также не требует наличия уникального идентификатора в таблице, к которой вы присоединяетесь, и работает намного быстрее, чем ответ, получивший наибольшее количество голосов. Вы также можете добавить критерии, для какой строки вы предпочитаете возвращать, а не просто брать случайную строку, используя предложение ORDER BY в подзапросе.

JosephDoggie
23 марта 2020 в 20:30
0

Это хорошее решение. Обратите внимание: при использовании для вашей собственной ситуации будьте очень осторожны при использовании PARTION BY (обычно вам, вероятно, нужен какой-то столбец идентификатора) и ORDER BY (что может быть сделано практически любым, в зависимости от того, какую строку вы хотите сохранить, например DateCreated desc будет одним из вариантов для некоторых таблиц, но это будет зависеть от многих вещей)

avatar
ernst
14 февраля 2013 в 21:47
3

Попробовал кросс, работает неплохо, но занимает чуть больше времени. Скорректированы столбцы строк, чтобы они были максимальными, и добавленная группа, которая сохраняла скорость и отбрасывала дополнительную запись.

Вот скорректированный запрос:

SELECT Orders.OrderNumber, max(LineItems.Quantity), max(LineItems.Description)
FROM Orders
    INNER JOIN LineItems 
    ON Orders.OrderID = LineItems.OrderID
Group by Orders.OrderNumber
Brianorca
4 августа 2015 в 23:52
13

Но указание максимума отдельно для двух столбцов означает, что количество не может быть связано с описанием. Если заказ был 2 виджета и 10 гаджетов, запрос вернет 10 виджетов.

avatar
Justin Fisher
6 апреля 2012 в 21:25
132

Я знаю, что на этот вопрос недавно был дан ответ, но при работе с большими наборами данных вложенные запросы могут быть дорогостоящими. Вот другое решение, при котором вложенный запрос будет запускаться только один раз, а не для каждой возвращаемой строки.

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
GER
20 января 2015 в 16:43
3

Это также намного быстрее, если ваш столбец LineItemId не проиндексирован должным образом. По сравнению с принятым ответом.

NickG
24 апреля 2015 в 16:04
5

Но как бы вы это сделали, если Max нельзя использовать, поскольку вам нужно упорядочить по столбцу, отличному от того, который вы хотите вернуть?

stifin
9 июня 2015 в 10:39
2

вы можете заказать производную таблицу любым способом и использовать TOP 1 в SQL Server или LIMIT 1 в MySQL

DotNetDublin
11 января 2021 в 16:27
1

Обнаружено, что это намного быстрее на больших наборах данных

George Menoutis
21 января 2021 в 18:15
1

Не могли бы вы уточнить? Что касается только синтаксиса, ваш ответ такой же вложенный, как и ответ Quassnoi: ровно один подзапрос. Вы не можете просто подразумевать, что один будет запускаться «для каждой возвращенной строки», а другой - не только потому, что синтаксис кажется таким . Вы должны включить план.

Justin Fisher
22 января 2021 в 21:35
0

@GeorgeMenoutis Насколько я понимаю, запрос типа bar = (SELECT ...) похож на выполнение подзапроса для каждой строки (пример: coderhelper.com/questions/2577174/join-vs-sub-query). В плане выполнения это заметно по количеству казней. Я предлагаю сравнить различные методы, чтобы определить, какой из них лучше всего работает в вашей ситуации. В моем случае метод, использованный в моем ответе, был значительно быстрее, чем методы подзапроса и перекрестного применения в принятом ответе при работе с большими наборами данных, и необходимо было упорядочить элементы LineItem (детерминированные).

avatar
Peter Radocchia
11 января 2010 в 16:59
4

РЕДАКТИРОВАТЬ: да ладно, у Квассного есть ответ получше.

Для SQL2K примерно так:

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
avatar
Tomalak
11 января 2010 в 16:50
28

Вы можете сделать:

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
  )

Для этого требуется индекс (или первичный ключ) на LineItems.LineItemID и индекс на LineItems.OrderID, иначе это будет медленным.

leo
2 июля 2015 в 09:53
2

Это не работает, если в Order нет LineItems. Затем подвыражение вычисляет LineItems.LineItemID = null и полностью удаляет левые порядки сущностей из результата.

Tomalak
2 июля 2015 в 09:58
8

Это также эффект внутреннего соединения, так что ... да.

leo
2 июля 2015 в 10:38
1

Решение, которое можно адаптировать для LEFT OUTER JOIN: coderhelper.com/a/20576200/510583

Tomalak
2 июля 2015 в 10:56
4

@leo Да, но OP сам использовал внутреннее соединение, поэтому я не понимаю вашего возражения.