sql—根据条件及其给定日期的“上一行”操作列检索最新行

mwg9r5ms 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(315)

数据集：

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
    DROP TABLE #temp

CREATE TABLE #temp(
user_id [int]  NOT NULL,
date [datetime] NOT NULL,
fieldid [int] NOT NULL,
fieldvalue NVARCHAR(100) NOT NULL,
fromvalue [int] NULL,
tovalue [int]  NULL,
action char(1) NOT NULL,
audit_date [datetime] NOT  NULL
)

Insert into #temp values ( 1,'2020-01-01',1,'a',NULL,0,'C','2020-01-01 21:00:39.000 ');

Insert into #temp values ( 2,'2020-01-01',1,'a',NULL,0,'C','2020-01-01 21:00:39.000 ');
Insert into #temp values ( 2,'2020-01-01',1,'a',NULL,0,'N','2020-01-01 22:00:39.000 ');

Insert into #temp values ( 2,'2020-01-01',1,'b',NULL,0,'C','2020-01-01 21:00:39.000 ');
Insert into #temp values ( 2,'2020-05-05',1,'a',NULL,0,'C','2020-05-05 21:00:39.000 ');
Insert into #temp values ( 2,'2020-05-05',2,'a',NULL,0,'C','2020-05-05 21:00:39.000 ');
Insert into #temp values ( 2,'2020-05-05',1,'b',NULL,0,'C','2020-05-05 21:00:39.000 ');
Insert into #temp values ( 2,'2020-05-05',3,'c',NULL,0,'C','2020-05-05 21:00:39.000 ');

Insert into #temp values ( 3,'2020-01-02',1,'a',NULL,0,'C','2020-01-01 10:00:39.000 ');
Insert into #temp values ( 3,'2020-01-02',1,'a',NULL,0,'N','2020-01-01 11:00:39.000 ');
Insert into #temp values ( 3,'2020-01-02',1,'a',NULL,0,'C','2020-01-01 12:00:39.000 ');

Insert into #temp values ( 4,'2020-10-10',1,'a',NULL,0,'C','2020-01-01 22:00:39.000 ');
Insert into #temp values ( 4,'2020-10-10',1,'a',1   ,0,'U','2020-01-01 23:00:39.000 ');`

对于给定的userid，date，fieldid，fieldvalue，给定的date，条件仅包括fromvalue=0和tovalue=1时的最新行
sql查询

with cte as
(
    select user_id, date, fieldid, fieldvalue, fromvalue, tovalue
        , action, audit_date
        ,  ROW_NUMBER() OVER(PARTITION BY user_id, date, fieldid, fieldvalue ORDER BY audit_date desc) AS 'rnk'
    from #temp (nolock)  
)
select * from cte where rnk = 1 and action <>'N'  and fromvalue IS NULL and  tovalue = 0 ;

我想我已经收到这个问题了。到目前为止还有效。请复查。
note:edited the 基于有效评论的原始帖子。我是新来的，我会张贴dtd以后。我的道歉

sql window-functions sql-server greatest-n-per-group tsql

来源：https://stackoverflow.com/questions/61990468/retrieve-latest-rows-based-on-the-condition-and-its-previous-rows-action-column

1条答案

按热度按时间

gopyfrb31#

你尝试使用 row_number() 但是，需要将整个过滤逻辑移动到子查询，在子查询中计算窗口函数。否则，与条件不匹配的行可能会排在第一位，然后在外部查询中被排除。
这应该是您想要的-请注意，您对过滤规范的描述与查询不匹配（我使用了查询的 predicate ，它们更好地匹配您的示例数据）：

select *
from (
    select
        t.*,
        row_number() over(
            partition by user_id, date, fieldid, fieldvalue
            order by audit_date desc
        ) rn
    from #temp t
    where fromvalue is null and tovalue = 0 and action <> 'N'
) t
where rn = 1

对于示例数据，这将生成：

user_id | date                    | fieldid | fieldvalue | fromvalue | tovalue | action | audit_date              | rn
------: | :---------------------- | ------: | :--------- | --------: | ------: | :----- | :---------------------- | :-
      1 | 2020-01-01 00:00:00.000 |       1 | a          |      null |       0 | C      | 2020-01-01 21:00:39.000 | 1 
      2 | 2020-01-01 00:00:00.000 |       1 | a          |      null |       0 | C      | 2020-01-01 21:00:39.000 | 1 
      2 | 2020-01-01 00:00:00.000 |       1 | b          |      null |       0 | C      | 2020-01-01 21:00:39.000 | 1 
      2 | 2020-05-05 00:00:00.000 |       1 | a          |      null |       0 | C      | 2020-05-05 21:00:39.000 | 1 
      2 | 2020-05-05 00:00:00.000 |       1 | b          |      null |       0 | C      | 2020-05-05 21:00:39.000 | 1 
      2 | 2020-05-05 00:00:00.000 |       2 | a          |      null |       0 | C      | 2020-05-05 21:00:39.000 | 1 
      2 | 2020-05-05 00:00:00.000 |       3 | c          |      null |       0 | C      | 2020-05-05 21:00:39.000 | 1 
      3 | 2020-01-02 00:00:00.000 |       1 | a          |      null |       0 | C      | 2020-01-01 12:00:39.000 | 1 
      4 | 2020-10-10 00:00:00.000 |       1 | a          |      null |       0 | C      | 2020-01-01 22:00:39.000 | 1

赞(0）回复(0）举报 2021-07-26

我来回答

sql—根据条件及其给定日期的“上一行”操作列检索最新行

1条答案

相关问题

热门标签

最新问答