打印日期范围之间的当前值和先前值 [英] Printing the current value and previous value between the date range
问题描述
我有这样的示例数据
ID DATE TIME STATUS
---------------------------------------------
A 01-01-2000 0900 ACTIVE
A 05-02-2000 1000 INACTIVE
A 01-07-2000 1300 ACTIVE
B 01-05-2005 1000 ACTIVE
B 01-08-2007 1050 ACTIVE
C 01-01-2010 0900 ACTIVE
C 01-07-2010 1900 INACTIVE
从上面的数据集中，如果我们只关注 ID='A'，我们注意到 A 最初是活跃的，然后在 05-02-2000 然后一直处于非活动状态，直到 01-07-2000.
From the above data set, if we only focus on ID='A' we note that A was initally active, then became inactive on 05-02-2000 and then it was inactive until 01-07-2000.
这意味着 A 从 05-Feb-2000 到 01-July-2000 处于非活动状态.
Which means that A was inactive from 05-Feb-2000 to 01-July-2000.
我的问题是:
如果我使用
(ID=A, Date=01-04-2000)执行查询，它应该给我
if I execute a query with
(ID=A, Date=01-04-2000)it should give me
A 05-02-2000 1000 INACTIVE
因为那个日期在那个数据集中不可用，它应该搜索前一个并打印
because since that date is not available in that data set, it should search for the previous one and print that
另外，如果我的条件是 (ID=A, Date=01-07-2000) 它不仅应该打印表中存在的值，还应该打印一个先前值
Also, if my condition is (ID=A, Date=01-07-2000) it should not only print the value which is present in the table, but also print a previous value
A 05-02-2000 1000 INACTIVE
A 01-07-2000 1300 ACTIVE
如果有人可以帮助我解决此查询，我将不胜感激.我正在尽力解决这个问题.
I would really appreciate if any one can assist me solve this query. I am trying my best to solve this.
谢谢大家.
对此有何看法?
常见问题
推荐答案
类似下面的内容应该可以:
Something like the following should work:
SELECT ID, Date, Time, Status
from (select ID, Date, Time, Status, row_number() over (order by Date) Ranking
from MyTable
where ID = @SearchId
and Date <= @SearchDate) xx
where Ranking < 3
order by Date, Time
这将最多返回两行.不清楚您是使用日期和时间数据类型的列，还是实际上使用保留字作为列名，因此您必须对此大惊小怪.(我省略了时间，但您可以轻松地将其添加到各种排序和过滤中.)
This will return at most two rows. Its not clear if you are using Date and Time datatyped columns, or if you are actually using reserved words as column names, so you'll have to fuss with that. (I left out Time, but you could easily add that to the various orderings and filterings.)
鉴于修订后的标准，它变得有点棘手，因为包含或排除一行取决于在不同行中返回的值.此处，如果有两行或更多行，则仅当第一"行等于特定值时才包括第二"行.执行此操作的标准方法是查询数据以获取最大值，然后在引用第一组结果的同时再次查询.
Given the revised criteria, it gets a bit trickier, as the inclusion or exclusion of a row depends upon the value returned in a different row. Here, the "second" row, if there are two or more rows, is included only if the "first" row equals a particular value. The standard way to do this is to query the data to get the max value, then query it again while referencing the result of the first set.
但是，您可以使用 row_number 做很多棘手的事情.解决这个问题:
However, you can do a lot of screwy things with row_number. Work on this:
SELECT ID, Date, Time, Status
from (select
ID, Date, Time, Status
,row_number() over (partition by case when Date = @SearchDate then 0 else 1 end
order by case when Date = @SearchDate then 0 else 1 end
,Date) Ranking
from MyTable
where ID = @SearchId
and Date <= @SearchDate) xx
where Ranking = 1
order by Date, Time
您必须解决日期/时间问题，因为这仅适用于日期.
You'll have to resolve the date/time issue, since this only works against dates.
这篇关于打印日期范围之间的当前值和先前值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
