时间戳

首页 > 解决方案 > CTE 递归连接表

sql - CTE 递归连接表

问题描述

我有一个 id 表，每次更新该 id 时都会使用状态代码和时间戳进行更新，例如：

ID       STATUS     TIMESTAMP
------------------------------------
12345    10         2020-08-01 11:00:01
12345    20         2020-08-01 11:01:24
12345    30         2020-08-01 11:07:42

我想将所有状态更改和时间排成一行，所以我最终得到：

ID       STATUS     TIMESTAMP           STATUS     TIMESTAMP           STATUS     TIMESTAMP
-----------------------------------------------------------------------------------------------------
12345    10         2020-08-01 11:00:01 20         2020-08-01 11:01:24 30         2020-08-01 11:07:42

我可以通过对每个状态进行连接来做到这一点，所以我的查询如下所示：

with T1 as
(SELECT distinct
id as 'ID',
Status as 'STATUS',
Timestamp as 'Time10'
from StatusHistory S
where TimeStamp > '2020-01-07 11:00'
and Timestamp < '2020-01-07 11:10'
and Status = '10'),

T2 as
(SELECT distinct
id as 'ID',
Status as 'STATUS',
Timestamp as 'Time20'
from StatusHistory S
where TimeStamp > '2020-01-07 11:00'
and Timestamp < '2020-01-07 11:10'
and Status = '20')

Select * from T1
join T2 on T1.ID = T2.ID

这行得通，但是我必须为每个状态都这样做，并且大约有 12 种不同的状态代码。我已经阅读了几个进行草书连接的示例，并且理解了这个概念，但是在将其实际应用于我的查询时遇到了很多麻烦。本质上，我希望能够说递归位是Status = Previous Status + 10，但如何实现这一点很麻烦。

我不明白如何获取最新状态并添加 10。

标签： sqlsql-servercommon-table-expressionrecursive-query

解决方案

您可以使用条件聚合：

select id,
       max(case when seqnum = 1 then status end) as status_1,
       max(case when seqnum = 1 then timestamp end) as timestamp_1,
       max(case when seqnum = 2 then status end) as status_2,
       max(case when seqnum = 2 then timestamp end) as timestamp_2,
       max(case when seqnum = 3 then status end) as status_3,
       max(case when seqnum = 3 then timestamp end) as timestamp_3
from (select sh.*,
             row_number() over (partition by id order by timestamp) as seqnum
      from StatusHistory sh
      where TimeStamp > '2020-01-07 11:00' and
            Timestamp < '2020-01-07 11:10'
     ) sh
group by id

推荐阅读