在 SQL Server 中查找最近的日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14023145/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find closest date in SQL Server
提问by MaxRecursion
I have a table dbo.Xwith DateTimecolumn Ywhich may have hundreds of records.
我有一个表dbo.X有DateTimecolumn Y可能有数以百计的记录。
My Stored Procedure has parameter @CurrentDate, I want to find out the date in the column Yin above table dbo.Xwhich is less than and closest to @CurrentDate.
我的存储过程有参数@CurrentDate，我想找出column Y上表dbo.X中小于和最接近的日期@CurrentDate.
How to find it?
如何找到它？
回答by ederbf
The where clause will match all rows with date less than @CurrentDate and, since they are ordered descendantly, the TOP 1 will be the closest date to the current date.
where 子句将匹配日期小于 @CurrentDate 的所有行，并且由于它们是按后代排序的，TOP 1 将是最接近当前日期的日期。
SELECT TOP 1 *
FROM x
WHERE x.date < @CurrentDate
ORDER BY x.date DESC
回答by Mikey Mouse
Use DateDiffand order your result by how many days or seconds are between that date and what the Input was
使用DateDiff并按该日期与输入之间的天数或秒数对结果进行排序
Something like this
像这样的东西
select top 1 rowId, dateCol, datediff(second, @CurrentDate, dateCol) as SecondsBetweenDates
from myTable
where dateCol < @currentDate
order by datediff(second, @CurrentDate, dateCol)
回答by Craig Gers
I have a better solution for this problem i think.
我认为这个问题有更好的解决方案。
I will show a few images to support and explain the final solution.
我将展示一些图像来支持和解释最终的解决方案。
BackgroundIn my solution I have a table of FX Rates. These represent market rates for different currencies. However, our service provider has had a problem with the rate feed and as such some rates have zero values. I want to fill the missing data with rates for that same currency that as closest in time to the missing rate. Basically I want to get the RateId for the nearest non zero rate which I will then substitute. (This is not shown here in my example.)
背景在我的解决方案中，我有一个外汇汇率表。这些代表不同货币的市场汇率。但是，我们的服务提供商在费率馈送方面存在问题，因此某些费率的值为零。我想用在时间上最接近缺失率的同一货币的利率来填充缺失的数据。基本上我想获得最近的非零利率的 RateId，然后我将替换它。（这在我的例子中没有显示在这里。）
1) So to start off lets identify the missing rates information:
1) 因此，首先让我们确定缺失的费率信息：
Query showing my missing rates i.e. have a rate value of zero
2) Next lets identify rates that are not missing.Query showing rates that are not missing
2) 接下来让我们确定没有遗漏的利率。查询显示未丢失的费率
3) This query is where the magic happens. I have made an assumption here which can be removed but was added to improve the efficiency/performance of the query. The assumption on line 26 is that I expect to find a substitute transaction on the same day as that of the missing / zero transaction. The magic happens is line 23: The Row_Number function adds an auto number starting at 1 for the shortest time difference between the missing and non missing transaction. The next closest transaction has a rownum of 2 etc.
3) 这个查询是神奇发生的地方。我在这里做了一个假设，可以删除它，但添加它是为了提高查询的效率/性能。第 26 行的假设是，我希望在丢失/零交易的同一天找到替代交易。神奇的事情发生在第 23 行：Row_Number 函数添加一个从 1 开始的自动编号，以实现丢失和非丢失事务之间的最短时间差。下一个最接近的事务的 rownum 为 2 等。
Please note that in line 25 I must join the currencies so that I do not mismatch the currency types. That is I don't want to substitute a AUD currency with CHF values. I want the closest matching currencies.
请注意，在第 25 行，我必须加入货币，以免货币类型不匹配。也就是说，我不想用 CHF 值替换 AUD 货币。我想要最匹配的货币。
Combining the two data sets with a row_number to identify nearest transaction
4) Finally, lets get data where the RowNum is 1The final query
4) 最后，让我们获取 RowNum 为 1的数据 最终查询
The query full query is as follows;
查询全查询如下；
; with cte_zero_rates as
(
Select *
from fxrates
where (spot_exp = 0 or spot_exp = 0)
),
cte_non_zero_rates as
(
Select *
from fxrates
where (spot_exp > 0 and spot_exp > 0)
)
,cte_Nearest_Transaction as
(
select z.FXRatesID as Zero_FXRatesID
,z.importDate as Zero_importDate
,z.currency as Zero_Currency
,nz.currency as NonZero_Currency
,nz.FXRatesID as NonZero_FXRatesID
,nz.spot_imp
,nz.importDate as NonZero_importDate
,DATEDIFF(ss, z.importDate, nz.importDate) as TimeDifferece
,ROW_NUMBER() Over(partition by z.FXRatesID order by abs(DATEDIFF(ss, z.importDate, nz.importDate)) asc) as RowNum
from cte_zero_rates z
left join cte_non_zero_rates nz on nz.currency = z.currency
and cast(nz.importDate as date) = cast(z.importDate as date)
--order by z.currency desc, z.importDate desc
)
select n.Zero_FXRatesID
,n.Zero_Currency
,n.Zero_importDate
,n.NonZero_importDate
,DATEDIFF(s, n.NonZero_importDate,n.Zero_importDate) as Delay_In_Seconds
,n.NonZero_Currency
,n.NonZero_FXRatesID
from cte_Nearest_Transaction n
where n.RowNum = 1
and n.NonZero_FXRatesID is not null
order by n.Zero_Currency, n.NonZero_importDate
回答by user1583384
CREATE PROCEDURE CurrentDate
@CurrentDate DATETIME
AS
BEGIN
Select * from orders
where OrderDate < @CurrentDate
END
GO
