sql-server - 如何找到sql server 2012中不同行的两个日期单元格之间的差异？

Question

我有一个 sql 表，每个记录的开始和结束时间如图所示。我有兴趣找到它们之间的区别。我通过使用 datediff(second, starttime, endtime) 找到了它们，但它给出了相同行的值的差异。但是我想看看下一个“日期/时间”是否从上一行结束“日期/时间”结束的地方开始，或者每行都有一些差距？我正在指示样本单元格，我想在其中找到差异(如果有)或 0。我想找到完整的列。谢谢

Answer 1

这是您要找的吗？

IF EXISTS (SELECT NULL FROM information_schema.TABLES WHERE TABLE_NAME = 'tblDateTimeDifference')
    DROP TABLE tblDateTimeDifference

CREATE TABLE tblDateTimeDifference (
    starts datetime, ends datetime
)

INSERT INTO tblDateTimeDifference
    SELECT '2015-02-19 00:00:00.000', '2015-02-19 00:01:00.000' UNION
    SELECT '2015-02-19 00:01:00.000', '2015-02-19 00:02:00.000' UNION
    SELECT '2015-02-19 00:02:00.000', '2015-02-19 00:03:00.000' UNION
    SELECT '2015-02-19 00:03:30.000', '2015-02-19 00:04:00.000' UNION
    SELECT '2015-02-19 00:04:00.000', '2015-02-19 00:05:00.000' UNION
    SELECT '2015-02-19 00:05:00.000', '2015-02-19 00:06:00.000' UNION
    SELECT '2015-02-19 00:06:00.000', '2015-02-19 00:07:00.000'
;
WITH cte AS (
    SELECT ROW_NUMBER() OVER (ORDER BY starts) AS row_num, starts, ends
    FROM tblDateTimeDifference
)
SELECT a.row_num AS compare_me, b.row_num AS to_me, a.ends AS compare_me_ends, b.starts AS compare_to_me_starts, DATEDIFF(ss, a.ends, b.starts) AS gap
FROM cte a INNER JOIN cte b ON a.row_num = b.row_num - 1

这是一个 fiddle :

http://sqlfiddle.com/#!3/9db054/1