如何选择最大日期的行，该日期小于其他日期？

I'm having hard times writing this T-SQL query, so I'd appreciate some help.

我很难写这个T-SQL查询,所以我很感激一些帮助。

There is a table that looks like this:

有一个看起来像这样的表:

id | idd (number) | created_on (datetime) | property | old_value | new_value | ... other

id | idd(数字)| created_on(datetime)|财产| old_value | new_value | ......其他

This is a history log reflecting changes made to fields of some objects.

这是一个历史日志,反映了对某些对象的字段所做的更改。

What I need to be able to do is to choose for every object the latest state of its given property to a given date.

我需要做的是为每个对象选择给定日期的给定属性的最新状态。

Suppose the data looks like this:

假设数据如下所示:

 
 1 | 10 | 2014-01-01 | 14 | null | 5  
 2 | 10 | 2014-01-03 | 14 | 5    | 10  
 3 | 10 | 2014-01-05 | 14 | 10   | 8  
 4 | 11 | 2014-01-02 | 14 | null | 7  
 5 | 11 | 2014-01-08 | 14 | 7    | 25  
 ...

This way if I have to select the latest states of property 14 at 2014-01-10, the result would be:

这样,如果我必须在2014-01-10选择房产14的最新状态,结果将是:

 3 | 10 | 2014-01-05 | 14 | 10   | 8  
 5 | 11 | 2014-01-08 | 14 | 7    | 25

If I have to select the latest states of property 14 at 2014-01-04, the result would be:

如果我必须在2014-01-04选择房产14的最新状态,结果将是:

 2 | 10 | 2014-01-03 | 14 | 5    | 10
 4 | 11 | 2014-01-02 | 14 | null | 7

What I've managed so far is to select the required values for a single object, which is quite easy, but I don't know how to extend the query to include all of them.

到目前为止我所管理的是为单个对象选择所需的值,这很容易,但我不知道如何扩展查询以包含所有这些值。

2 个解决方案

#1

You could use ROW_NUMBER()

你可以使用ROW_NUMBER()

DECLARE @as_of_date DATE = '2014-01-04'

;WITH cte AS ( 
    SELECT  [id], [idd], [created_on], [property], [old_value], [new_value], rn=ROW_NUMBER() OVER (PARTITION BY idd ORDER BY created_on DESC)
    FROM    YourTable
    WHERE created_on <= @as_of_date
)
SELECT  *
FROM    cte
WHERE rn = 1

#2

You can use either common table expression or subquery:

您可以使用公用表表达式或子查询:

;WITH CTE1 (created_on, idd)
AS (
    SELECT MAX(created_on), idd 
    FROM Table_1 
    WHERE created_on < @DateTo
    GROUP BY idd)
SELECT t1.* FROM Table_1 t1
INNER JOIN CTE1 ON t1.created_on = CTE1.created_on and t1.idd = CTE1.idd
ORDER BY idd;


SELECT * FROM Table_1 t1
WHERE t1.created_on = (SELECT MAX(created_on) FROM Table_1 t2 where t1.idd = t2.idd AND created_on < @DateTo)
ORDER BY idd;

#1