使用条件在一段时间内获取一条记录

Question

我有一个创建对象更改历史的大型查询。简而言之，它的结果如下所示：

id     changedOn      recordtype
1      2019-12-5      history
1      2020-01-1      history
1      2020-01-7      actual
2      2018-10-9      history

我想要的结果是：

id     changedOn      recordtype
1      2019-12-5      history
1      2020-01-7      actual
2      2018-10-9      history

如果每个id在同一个月有2条记录，我想省略这个月的历史记录。

如果可能的话，我想避免使用cursor。但是我被卡住了。

Answer 1

如果您希望每月有一条记录，并且首选参数为"actual"，那么可以使用row_number()

select t.*
from (select t.*,
             row_number() over (partition by id, year(changedOn), month(changedOn) order by recordtype) as seqnum
      from t
     ) t
where seqnum = 1;

如果您想要一个月的所有“实际”记录--如果没有--所有历史记录，我建议这样的逻辑：

select t.*
from t
where t.recordtype = 'actual' or
      (t.recordtype = 'history' and
       not exists (select 1
                   from t t2
                   where t2.id = t.id and
                         t2.recordtype = 'actual' and
                         year(t2.changedon) = year(t.changedon) and
                         month(t2.changedon) = month(t.changedon) 
                   );

这两种方法略有不同。但是，如果您在一个月内对单个id有多个“实际”或“历史”，您才会注意到差异。

Answer 2

只需删除不是最新的带有changedOn的记录

        select * from tbl a 
    where not exists
(select 1 from tbl b where a.id = b.id and a.recordtype = b.recordtype and a.changedOn < b.changedOn )