如何获得具有T-SQL的一组值的首次出现

发布于 2025-02-11 04:44:48 字数 1152 浏览 2 评论 0原文

在下面有一个SQL Server DB表，我如何获取与第一次出现一系列值（Tablea和TableB_S已用户ID链接）相关的数据 - 我需要第一次出现isActive = 1的最后一个系列 value（表中的第五行，1000 | 1 | 2022-02-18 10:23:01）：

tableb_s

userId	iSARTIVE	DATE
1000	0	2021-10-11 13:23 ：00
1000	0	2021-11-11 15:23:12
1000	1	2021-11-10 12:23:32
1000	0	2022-01-02 09:23:56
1000	1	2022-02-02-18 10:23:011111
1000	1	2022-02-22 13:23:12
1000	1	2022-03-23 18:23:13

下面的查询返回与值isactive = 1 （1000）返回的数据。

select a.*, ca.UserId, ca.Date
from TableA a

cross apply (select top 1 s.UserId, s.Date 
            from TableB_S s where s.UserId = a.UserId
        order by s.Date desc) ca

| 1000   | 1         |  2022-02-18 10:23:01 |

原文

Having a SQL Server DB table below how can I get the data related to the first occurrence of a series of values (TableA and TableB_S have UserId linked) - I need to get the first occurrence of the last series of IsActive = 1 value (the 5th row in the table, 1000 |1 | 2022-02-18 10:23:01):

TableB_S

UserId	IsActive	Date
1000	0	2021-10-11 13:23:00
1000	0	2021-11-11 15:23:12
1000	1	2021-11-10 12:23:32
1000	0	2022-01-02 09:23:56
1000	1	2022-02-18 10:23:01
1000	1	2022-02-22 13:23:12
1000	1	2022-03-23 18:23:13

The query below returns the data related to the last occorrence of the value IsActive = 1 (1000 | 1 | 2022-03-23 18:23:13)

select a.*, ca.UserId, ca.Date
from TableA a

cross apply (select top 1 s.UserId, s.Date 
            from TableB_S s where s.UserId = a.UserId
        order by s.Date desc) ca

How to get this data ?

| 1000   | 1         |  2022-02-18 10:23:01 |

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

杀お生予夺 2025-02-18 04:44:48

这里的逻辑是“对于每个用户获取启动一系列1的行（即，用户的日期顺序的上一行为零或不存在），那么该集合获得最高日期”。

select   UserId, IsActive, max([Date])
from     (
            select   UserId, 
                     IsActive, 
                     [Date],
                     PriorActive =  lag(IsActive, 1, 0) 
                                    over 
                                    (
                                       partition by UserId 
                                       order by [Date] asc
                                    )
            from     #TableB_S
         ) t
where    t.IsActive = 1 
         and t.PriorActive = 0
group by UserId, IsActive

The logic here is "for each user get the set of rows which start a series of 1's (ie, the previous row in date order for the user is a zero, or doesn't exist), then of that set get the highest date".

select   UserId, IsActive, max([Date])
from     (
            select   UserId, 
                     IsActive, 
                     [Date],
                     PriorActive =  lag(IsActive, 1, 0) 
                                    over 
                                    (
                                       partition by UserId 
                                       order by [Date] asc
                                    )
            from     #TableB_S
         ) t
where    t.IsActive = 1 
         and t.PriorActive = 0
group by UserId, IsActive

回复收藏 0 原文

~没有更多了~