Как проверить наличие связанных записей в группе по запросу?
Есть 3 простые таблицы Orders, OrderLines и OrderLineRealizations с очевидными связями. Я хочу вернуть некоторую информацию о заказах с дополнительным столбцом, который отображает наличие реализации какой-либо строки для данной группы.
Упрощенный запрос выглядит так:
SELECT
Orders.Id,
SUM(OrderLines.Quantity) AS TotalAmount,
-- Is there a realization?
FROM Orders
INNER JOIN OrderLines ON OrderId = Orders.Id
GROUP BY Orders.Id -- some additional grouping
Я пытался:
SELECT
Orders.Id,
SUM(OrderLines.Quantity) AS TotalAmount,
CASE
WHEN EXISTS (SELECT * FROM OrderLineRealizations
WHERE OrderLineId IN (OrderLines.Id))
THEN 1
ELSE 0
END AS RealizationExists
FROM Orders
INNER JOIN OrderLines ON OrderId = Orders.Id
GROUP BY Orders.Id
Но использовать IN таким образом нельзя. Я также не могу использовать Left Join для реализаций, потому что тогда я получаю неправильный TotalAmount.
Результат, которого я хочу достичь:
Orders
- - - -
Id
1
2
OrderLines
- - - -
Id | OrderId | Quantity
1 | 1 | 10
2 | 1 | 15
3 | 2 | 11
OrderLineRealizations
- - - -
Id | OrderLineId |
1 | 1 |
Result
- - - -
OrderId | TotalAmount | RealizationExists
1 | 25 | 1
2 | 11 | 0
Какой я могу написать запрос, чтобы получить ожидаемый результат?
Ответы 4
SELECT
Id,
SUM(Quantity) AS TotalAmount,
max(RealizationExists) as RealizationExists
from (
SELECT
Orders.Id,
OrderLines.Quantity,
CASE
WHEN EXISTS (SELECT * FROM OrderLineRealizations olr
where olr.OrderLineId = OrderLines.Id)
THEN 1
ELSE 0
END as RealizationExists
FROM Orders
INNER JOIN OrderLines ON OrderId = Orders.Id
) tmp
group by id;
Похоже, вы прочитали вопрос лучше, чем я. Проголосовал за.
Спасибо, только что проверил, работает отлично.
Безопасный способ — GROUP BY и агрегировать производную таблицу перед объединением:
SELECT
Orders.Id,
ol.TotalAmount,
CASE WHEN EXISTS (SELECT * FROM OrderLineRealizations olr
where olr.OrderLineId = ol.OrderId) THEN 1
ELSE 0
END as RealizationExists
FROM Orders
INNER JOIN (select OrderId, sum(Quantity) AS TotalAmount
from OrderLines
GROUP BY OrderId) ol ON ol.OrderId = Orders.Id
Демо: https://dbfiddle.uk/8yejSpeu (на основе скрипки @Cetin Basoz, спасибо!)
Тот, который я предоставил, уже был в безопасности :)
@CetinBasoz, правда. Однако совет сначала GROUP BY, а затем JOIN легко запомнить, и он никогда не подводит.
Хороший совет, даже не подумал об этом, спасибо!
Да, я обычно так и делаю. Иногда они очень взаимозаменяемы :)
Извините, у меня нет под рукой sql-сервера, поэтому я не могу проверить точный формат, но вы можете выполнить левое соединение и получить только 1 строку.
SELECT
Orders.Id,
SUM(OrderLines.Quantity) AS TotalAmount,
CASE
WHEN F3.OrderLineId is not null
THEN 1
ELSE 0
FROM Orders F1
INNER JOIN OrderLines F2
ON F1.Id = F2.OrderId
left join OrderLineRealizations F3
ON F2.OrderId = F3.Id
where (F3.id = (SELECT O2.id FROM OrderLineRealizations O2 WHERE O2.Id = F2.OrderId FETCH FIRST 1 ROWS ONLY) )
GROUP BY Orders.Id --
Как я уже сказал, я не могу проверить это за вас, но левое соединение должно работать. Если вы ищете объединение, возвращающее примеры из 1 строки.
= = =
Приведенный ниже пример протестирован на dbfiddle.uk согласно совету @Cetin Basoz — Спасибо.
SELECT
Orders.Id as OrderId
,
SUM(OrderLines.Quantity) AS TotalAmount
,
case
when OrderLineRealizations.Id > 0
then '1'
else '0'
end OrderLineRealizationExists
FROM Orders
INNER JOIN OrderLines
ON Orders.Id = OrderLines.OrderId
left join OrderLineRealizations
ON Orders.Id = OrderLineRealizations.Id
where (
(OrderLineRealizations.OrderLineId = ( select max(RR.OrderLineId) from OrderLineRealizations RR where OrderLineRealizations.Id = RR.Id )
)
or OrderLineRealizations.Id is null
)
GROUP BY Orders.id , OrderLineRealizations.Id
ORDER BY Orders.id
;
Вам не нужен SQL-сервер для тестирования. Вы можете использовать dbfiddle.uk, sqlfiddle.com, db-fiddle.com... И к вопросу уже были ответы с прикрепленным демонстрационным кодом.
Синтаксическая ошибка: выражение CASE не имеет END. Кроме того, по какой-то странной причине SQL Server требует предложение OFFSET, если вы выполняете FETCH FIRST. (Нестандартное поведение.)
Кстати, F1 и F2 — плохие псевдонимы таблиц. Выберите что-нибудь разумное, например o для заказов, ol для строк заказов и т. д.
Вдохновленный решением Цетина Базоса, я нашел еще одно, более простое решение:
SELECT O.Id, SUM(L.Quantity) AS TotalAmount, MAX(CASE WHEN Tmp.Id IS NULL THEN 0 ELSE 1 END) AS RealizationExists
FROM Orders O
INNER JOIN OrderLines L ON L.OrderId = O.Id
LEFT JOIN (
SELECT TOP 1 R.OrderLineId, R.Id FROM OrderLineRealizations R
) Tmp ON Tmp.OrderLineId = L.Id
GROUP BY O.Id
При объединении в группу с помощью агрегирования вы можете выполнить GROUP BY в подзапросе перед присоединением.