Поиск записей по ближайшей дате SQL

#sql-server #tsql #sql-server-2012

#sql-сервер #tsql #sql-server-2012

Вопрос:

У меня есть таблица dbo.X со DateTime столбцом lastUpdated и столбцом code product CodeProd , в котором могут содержаться сотни записей, причем CodeProd дублируется, поскольку таблица используется как «история запасов»

Моя хранимая процедура имеет параметр @Date , я хочу получить все CodeProd ближайшие к этой дате, так что, например, если у меня есть:

  ---------- -------------- -------- 
| CODEPROD | lastUpdated  | STATUS |
 ---------- -------------- -------- 
|       10 |   2-1-2019   |   C1   |
|       10 |   1-1-2019   |   C2   |
|       10 |   31-12-2019 |   C1   |
|       11 |   31-12-2018 |   C1   |
|       11 |   30-12-2018 |   C1   |
|       12 |   30-8-2018  |   C3   |
 ---------- -------------- --------

и @Date = ‘1-1-2019’

Я хочу получить:

  ---- -------------- ------ 
| 10 |    1-1-2019  |   C2 |
| 11 |   31-12-2018 |   C1 |
| 12 |   30-8-2018  |   C3 |
 ---- -------------- ------

Как это найти?

1.Что произойдет, если в CODEPROD есть 2 или более строк «ближайших»? Например, есть строки со значением 20190102 и 20181231 для CODEPROD 13 . Должна ли быть возвращена строка для 20190102 или 20181231 ? Оба?

2. @Larnu Он должен вернуть запись с 20181231

3. Под ближайшей вы подразумеваете меньшую, ближайшую к дате, или меньшую / большую, ближайшую к дате.

4. @SalmanA меньшее значение, ближайшее меньшее значение, ближайшее к этой дате. например: если @Date есть 20190101 , и у меня есть 20181231 и 20190102 я хочу получить 20181231

Ответ №1:

Вы можете использовать TOP(1) WITH TIES , чтобы получить одну строку с ближайшей датой для каждой CODEPROD , которая должна быть меньше указанной даты.

Попробуйте использовать следующий код.

 SELECT TOP(1) WITH TIES * 
FROM   [YourTableName] 
WHERE  lastupdated <= @date 
ORDER  BY Row_number() 
            OVER ( 
              partition BY [CODEPROD] 
              ORDER BY lastupdated DESC);

1. Спасибо! Один вопрос, братан… Даже если я добавлю ` WITH (NOLOCK)` и индекс для [CODEPROD] времени выполнения запросов, чтобы он все еще был слишком тяжелым для выполнения. Есть ли подсказка, как оптимизировать SQL?

Ответ №2:

Вы можете использовать apply :

 select distinct t.CODEPROD, t1.lastUpdated, t1.STATUS
from table t cross apply
     ( select top (1) t1.*
       from table t1 
       where t1.CODEPROD = t.CODEPROD and t1.lastUpdated <= @date
       order by t1.lastUpdated desc
     ) t1;

1. Ваш TSQL работает очень быстро по сравнению с PSK ответом с помощью SELECT TOP(1) WITH TIES * FROM [YourTableName] WHERE lastupdated <= @date ORDER BY Row_number() OVER ( partition BY [CODEPROD] ORDER BY lastupdated DESC); , так что большое вам спасибо!