代码之家 › 专栏 › 技术社区 › priyanka.sarkar

递归CTE以查找当前和以前的工作状态以及工作状态的输入时间

sql-server-2016 tsql sql-server sql

0

priyanka.sarkar · 技术社区 · 7 年前

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

意味着我需要根据客户情况捕获当前和以前的工作状态以及工作状态的进入时间。

我已尝试使用以下递归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

2 回复 | 直到 7 年前

1

Zeki Gumus 7 年前

就像安德鲁说的 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

2

0

halfer 7 年前

(代表问题作者发布)

领导功能解决了这个问题。以下是输出:

select 
    CustomerId,
    PresentWorkState = WorkState,
    PresentStatechangedate = statechangedate,
    PreviousWorkState = LEAD(WorkState) OVER (partition by CustomerId ORDER BY stateorder) ,
    PreviousStatechangedate = LAG(statechangedate) OVER (partition by CustomerId ORDER BY stateorder)
from @t