用于查找WorkState的当前和先前的WorkState和条目时间的递归CTE
用于查找WorkState的当前和先前的WorkState和条目时间的递归CTE
提问于 2018-12-14 17:52:01
我有以下内容
该模式如下所示
declare @t table(CustomerId varchar(10),WorkState varchar(10),statechangedate datetimeoffset, stateorder int)
insert into @t
select '1','WorkStateA','2018-10-30 13:38:53.5133333 +00:00',1 union all
select '2','WorkStateA','2018-05-18 17:04:56.9900000 +00:00',1 union all
select '2','WorkStateA','2018-05-18 16:22:20.3266667 +00:00',2 union all
select '2','WorkStateB','2018-05-09 12:46:33.8300000 +00:00',3 union all
select '3','WorkStateF','2018-06-21 12:40:03.2933333 +00:00',1 union all
select '3','WorkStateE','2018-06-21 12:38:43.9000000 +00:00',2 union all
select '3','WorkStateD','2018-06-21 12:38:24.7533333 +00:00',3 union all
select '3','WorkStateC','2018-06-21 12:38:11.0233333 +00:00',4 union all
select '3','WorkStateB','2018-06-21 12:38:04.1933333 +00:00',5 union all
select '3','WorkStateA','2018-06-21 12:36:51.4633333 +00:00',6
select * from @t我要找的是
意味着我需要在客户的基础上捕获当前和以前的WorkState以及WorkState的输入时间。
我已经尝试了下面的递归CTE
;with cte as(
select
t.CustomerId,
PresentWorkState = t.WorkState,
PresentStatechangedate = t.statechangedate,
t.stateorder,
PreviousWorkState = null ,
PreviousStatechangedate= null
from @t t where t.stateorder=1
union all
select
t1.CustomerId,
t1.WorkState,
t1.statechangedate,
c.stateorder,
c.PreviousWorkState,
c.PreviousStatechangedate
from @t t1
join cte c on t1.stateorder !=c.stateorder)
select *
from cte但是做不到。
回答 1
Stack Overflow用户
回答已采纳
发布于 2018-12-14 18:22:11
就像安德鲁说的那样，LAG()应该为你的目的工作，而不是使用递归的cte。请检查以下内容：
SELECT *
,LAG(WorkState) OVER(PARTITION BY CustomerId ORDER BY statechangedate)
,LAG(statechangedate) OVER(PARTITION BY CustomerId ORDER BY statechangedate)
FROM @T
ORDER BY CustomerId,statechangedate页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/53777256
复制相关文章
相似问题