Как выполнить JOIN с первой строкой?

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

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

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

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

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, иначе запрос может выполняться медленно.

Для 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

Этот запрос использует функцию FIRST_VALUE() совместно с оператором OVER и PARTITION BY, чтобы получить первое значение Quantity и Description для каждой группы заказов, отсортированных по описанию позиций в заказе. Убедитесь, что таблицы Orders и LineItems связаны через OrderID.

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

WITH firstOnly AS (
    SELECT 
        Orders.OrderNumber, 
        LineItems.Quantity, 
        LineItems.Description, 
        ROW_NUMBER() OVER (PARTITION BY Orders.OrderID ORDER BY Orders.OrderID) AS 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 = o.OrderID 
        FOR XML PATH('')) AS NVARCHAR(MAX)) AS lines
) AS l

Этот запрос объединяет описания всех позиций заказа в одну строку, разделенную запятой. Выберите подходящий для вашей задачи метод!

Коррелированные подзапросы — это подзапросы, которые зависят от внешнего запроса. Это похоже на цикл 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
)

В этом случае для каждой строки из таблицы users подзапрос будет возвращать id последнего виджета (widgets), связанного с соответствующим пользователем, что позволяет объединить результаты.