作者carsun00 (永夜)
看板Database
标题[SQL ] 时间RowData转甘特图
时间Fri May 17 23:57:05 2019
资料库名称：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
--
※ 发信站: 批踢踢实业坊(ptt.cc), 来自: 59.120.185.97
※ 文章网址: https://webptt.com/cn.aspx?n=bbs/Database/M.1558108627.A.109.html
※ 编辑: carsun00 (111.82.151.25), 05/18/2019 11:59:23