SQL Получить ближайшую дату в обоих направлениях
Я пытаюсь найти эффективный способ запроса таблицы, мне нужно вывести строку, ближайшую к заданной дате, в обоих направлениях, заметьте.
У меня есть решение на ближайший день в будущем
SELECT top 1 id
FROM [dbo].[Shipments]
WHERE EstimatedArrivalDate < @date
AND Complete = 0
AND (RegNoTrailer = @regNo OR RegNoTruck = @regNo)
ORDER BY EstimatedArrivalDate DESC
Это ближайшая дата в будущем, но мне также нужно проверить прошлые даты.
Ответы 5
Это сработает?
SELECT top 1 id
FROM [dbo].[Shipments]
WHERE EstimatedArrivalDate > @date --change to >
AND Complete = 0
AND (RegNoTrailer = @regNo OR RegNoTruck = @regNo)
ORDER BY EstimatedArrivalDate ASC --change to ASC
Или оба вместе, используя UNION
SELECT top 1 id
FROM [dbo].[Shipments]
WHERE EstimatedArrivalDate < @date
AND Complete = 0
AND (RegNoTrailer = @regNo OR RegNoTruck = @regNo)
ORDER BY EstimatedArrivalDate DESC
UNION
SELECT top 1 id
FROM [dbo].[Shipments]
WHERE EstimatedArrivalDate > @date --change to >
AND Complete = 0
AND (RegNoTrailer = @regNo OR RegNoTruck = @regNo)
ORDER BY EstimatedArrivalDate ASC --change to ASC
Вы можете использовать Datediff и Abs в комбинации.
select abs(DATEDIFF(second,<dateColumn>,<dateParameter>)) result,<otherColumns>
from <tableName> order by result
Первая запись будет самой близкой.
Как я спросил Рафаэля, не будет ли это неэффективным для больших таблиц?
@JonasOlesen Конечно, производительность снизится, потому что вы будете сканировать таблицу все время (если в предложении where нет индекса по другим столбцам). Альтернативный вариант - использовать объединения, как и в другом ответе, но это потребует выполнения нескольких запросов. Вы можете сравнить их и оценить сами. Да и раньше Datediff вела себя плохо. Не уверен, что это изменилось \.
Попробуйте использовать функцию РАЗНДАТ, чтобы найти разницу между двумя датами. Вам также потребуется добавить функцию ABS (), чтобы обслуживать как прошлые, так и будущие даты.
SELECT top 1 id
FROM [dbo].[Shipments]
WHERE Complete = 0
AND (RegNoTrailer = @regNo OR RegNoTruck = @regNo)
ORDER BY ABS(DATEDIFF(D,EstimatedArrivalDate,@date)) ASC
Насколько эффективным было бы запускать dateiff на большом столе?
Используйте DATEDIFF () и ABS (), чтобы получить ближайшую разницу дат
SELECT top 1 id
FROM [dbo].[Shipments]
WHERE Complete = 0
AND (RegNoTrailer = @regNo OR RegNoTruck = @regNo)
ORDER BY ABS(DATEDIFF(SECOND, @date, EstimatedArrivalDate))
Если вы хотите, чтобы это было эффективно, вам понадобятся правильные индексы. Если это так, я думаю, что может быть более сложный запрос:
SELECT TOP 1 id
FROM ((SELECT TOP 1 id, EstimatedArrivalDate
FROM [dbo].[Shipments]
WHERE EstimatedArrivalDate < @date AND
Complete = 0
RegNoTrailer = @regNo
ORDER BY EstimatedArrivalDate DESC
) UNION ALL
(SELECT TOP 1 id, EstimatedArrivalDate
FROM [dbo].[Shipments]
WHERE EstimatedArrivalDate < @date AND
Complete = 0
RegNoTruck = @regNo
ORDER BY EstimatedArrivalDate DESC
) UNION ALL
(SELECT TOP 1 id, EstimatedArrivalDate
FROM [dbo].[Shipments]
WHERE EstimatedArrivalDate > @date AND
Complete = 0
RegNoTrailer = @regNo
ORDER BY EstimatedArrivalDate ASC
) UNION ALL
(SELECT TOP 1 id, EstimatedArrivalDate
FROM [dbo].[Shipments]
WHERE EstimatedArrivalDate > @date AND
Complete = 0
RegNoTruck = @regNo
ORDER BY EstimatedArrivalDate ASC
)
)
ORDER BY ABS(DATEDIFF(second, EstimatedArrivalDate, @Date));
Для этого запроса вам нужны два индекса: Shipments(RegNoTruck, Complete, EstimatedArrivalDate) и Shipments(RegNoTrailer, Complete, EstimatedArrivalDate).
Разделив условия OR на два отдельных подзапроса, каждый подзапрос может использовать свой индекс.
Спасибо, это сработало отлично после нескольких исправлений синтаксиса :) спасибо
О.о. Интересно, так ли это неэффективно, как кажется, или же подготовка сводит это к чему-то более связному. Что-то мне подсказывает, что SQL не предназначен для использования.
@ Дмитрий. . . Такая структура запроса позволяет использовать индексы для сокращения набора данных для четырех случаев, подразумеваемых исходным запросом. Есть более лаконичные способы выразить логику; эта версия позволяет оптимизатору легко использовать доступные индексы эффективно.
А как насчет добавления
UNION ALL SELECT TOP 1 .... > @date ... ORDER BY EstimatedArrivalDate?