我有以下内容
该模式如下所示
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
但是做不到。
https://stackoverflow.com/questions/53777256
复制相似问题