使用mssql选择记录链
使用mssql选择记录链
提问于 2016-08-04 13:50:32
我在TimeLines中有一个包含记录的表，我需要获取组成45分钟链集的行。
1|2016-01-01 00:00
2|2016-01-01 00:30
3|2016-01-01 00:45
4|2016-01-01 01:00我如何根据它的时间找到第二行，因为第二行，第三行和第四行是45分钟集合的不可分解的15分钟的时间线链?第一行和第二行是不好的，因为时间线之间的间隔是30分钟。
第2行、第3行和第4行是一致的时间线。第二排加15分钟-好的。原因是当时存在第三行。第三排加15分钟-好的。因为当时存在第四排。因此，我有45分钟一致的时间线链。
1行加15分钟-不好。原因00:15日期不存在的时间。
回答 3
Stack Overflow用户
发布于 2016-08-04 14:13:33
尝尝这个
DECLARE @Tbl TABLE (Id INT, StartDate DATETIME)
INSERT INTO @Tbl
VALUES
(1,'2016-01-01 00:00'),
(2,'2016-01-01 00:30'),
(3,'2016-01-01 00:45'),
(4,'2016-01-01 01:00')
;WITH CTE
AS
(
SELECT
Id ,
StartDate,
ROW_NUMBER() OVER (ORDER BY Id) AS RowId
FROM
@Tbl
)
SELECT
CurRow.*,
CASE
WHEN
DATEDIFF(MINUTE, CurRow.StartDate, NextRow.StartDate ) = 15 OR
DATEDIFF(MINUTE, PrevRow.StartDate, CurRow.StartDate ) = 15
THEN '15 MIN'
ELSE 'NO' END Flag
FROM
CTE CurRow LEFT JOIN
(SELECT *, C.RowId - 1 AS TmpRowId FROM CTE C) NextRow ON CurRow.RowId = NextRow.TmpRowId LEFT JOIN
(SELECT *, C.RowId + 1 AS TmpRowId FROM CTE C) PrevRow ON CurRow.RowId = PrevRow.TmpRowId输出：
Id StartDate RowId Flag
1 2016-01-01 00:00:00.000 1 NO
2 2016-01-01 00:30:00.000 2 15 MIN
3 2016-01-01 00:45:00.000 3 15 MIN
4 2016-01-01 01:00:00.000 4 15 MINStack Overflow用户
发布于 2016-08-04 14:37:47
如果我没理解错的话，你可以使用领先/滞后：
WITH Src AS
(
SELECT * FROM (VALUES
(1,'2016-01-01 00:00'),
(2,'2016-01-01 00:30'),
(3,'2016-01-01 00:45'),
(4,'2016-01-01 01:00')) T(ID, [Date])
)
SELECT *, CASE WHEN LEAD([Date]) OVER (ORDER BY ID)=DATEADD(MINUTE, 15, [Date])
OR LAG([Date]) OVER (ORDER BY ID)=DATEADD(MINUTE, -15, [Date])
THEN 'Chained' END [Status]
FROM Src它产生：
ID Date Status
-- ---- ------
1 2016-01-01 00:00 NULL
2 2016-01-01 00:30 Chained
3 2016-01-01 00:45 Chained
4 2016-01-01 01:00 ChainedStack Overflow用户
发布于 2016-08-04 15:02:01
您可以使用外部应用和棘手的ROW_NUMBER()来完成此操作：
;WITH TimeLines AS ( --This CTE is similar to your table
SELECT *
FROM (VALUES
(1, '2016-01-01 00:00'),(2, '2016-01-01 00:30'),
(3, '2016-01-01 00:45'),(4, '2016-01-01 01:00'),
(5, '2016-01-01 01:05'),(6, '2016-01-01 01:07'),
(7, '2016-01-01 01:15'),(8, '2016-01-01 01:30'),
(9, '2016-01-01 01:45'),(10, '2016-01-01 02:00')
) as t(id, datum)
)
, cte AS (
SELECT t.id,
t.datum,
CASE WHEN ISNULL(DATEDIFF(MINUTE,t1.datum,t.datum),0) != 15 THEN DATEDIFF(MINUTE,t.datum,t2.datum) ELSE 15 END as i
FROM TimeLines t --in this cte with the help of
OUTER APPLY ( --OUTER APPLY we are getting next and previous dates to compare them
SELECT TOP 1 *
FROM TimeLines
WHERE t.datum > datum
ORDER BY datum desc) t1
OUTER APPLY (
SELECT TOP 1 *
FROM TimeLines
WHERE t.datum < datum
ORDER BY datum asc) t2
)
SELECT *, --this is final select to get rows you need with chaines
(ROW_NUMBER() OVER (ORDER BY (SELECT 1))+2)/3 as seq
FROM cte
WHERE i = 15输出：
id datum i seq
2 2016-01-01 00:30 15 1
3 2016-01-01 00:45 15 1
4 2016-01-01 01:00 15 1
7 2016-01-01 01:15 15 2
8 2016-01-01 01:30 15 2
9 2016-01-01 01:45 15 2
10 2016-01-01 02:00 15 3页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/38759366
复制相关文章
相似问题
腾讯云开发者
