数据库名称：SQL Server 2017
数据库版本：14.0.2002.14
内容/问题描述：
资料来源是状态&Time的资料表
需要转换成开始与结束的资料格式
同时可能会有多笔开始多笔结束
要取出最早时间与最晚时间
是有想出一个堪用的语法...
但是想知道有没有比较好的做法..
资料如下
User SEQ Time
AA 1 2019-05-10 09:00:00.000
AA 1 2019-05-10 20:00:00.000
AA 2 2019-05-11 10:00:00.000
AA 2 2019-05-11 20:00:00.000
人| Seq|开始 | 结束
AA| 1 |2019-05-10 09:00:00.000 | 2019-05-11 10:00:00.000
AA| 2 |2019-05-11 10:00:00.000 | 现在时间
seq2的起始时间要当作 seq1的结束时间。
没有下一笔资料，抓现在GetDate()
SQL语法如下
SELECT
StartData.[User]
, StartData.[Start_Date_Time]
, EndData.[End_Date_Time]
FROM
( SELECT
ROW_NUMBER() OVER(PARTITION BY [User] ORDER BY
CONVERT(VARCHAR(20), [Time], 111)) AS Num
, 1 + ROW_NUMBER() OVER(PARTITION BY [User] ORDER BY
CONVERT(VARCHAR(20), [Time], 111)) AS Num2
, [User]
, CONVERT(VARCHAR(20), [Time], 111) AS [Start_Date_Time]
FROM
[TEST]
GROUP BY
[User]
, CONVERT(VARCHAR(20), [Time], 111)
) AS StartData
LEFT JOIN
( SELECT
ROW_NUMBER() OVER(PARTITION BY [User] ORDER BY
CONVERT(VARCHAR(20), [Time], 111)) AS Num3
, [User], CONVERT(VARCHAR(20), [Time], 111) AS [End_Date_Time]
FROM
[TEST]
GROUP BY
[User]
, CONVERT(VARCHAR(20), [Time], 111)
) AS EndData
ON StartData.[User] = EndData.[User]
AND StartData.Num2 = EndData.Num3