SQL Server：比较行之间的日期

我正在使用SQL Server 2016,我无法弄清楚如何构建该查询.

假设我有一个这样的表格：

ID  EntryTime               ResultTime
1   2016-05-02 13:30:00     2016-05-02 21:50:00
2   2016-05-02 14:45:00     2016-05-02 22:00:00
3   2016-05-02 16:30:00     2016-05-02 22:21:00
4   2016-05-03 01:00:00     2016-05-03 03:33:00
5   2016-05-03 10:30:00     2016-05-04 07:47:00
6   2016-05-03 12:30:00     2016-05-03 22:45:00
7   2016-05-04 11:30:00     2016-05-05 21:30:00
8   2016-05-04 12:30:00     2016-05-04 22:58:00
9   2016-05-04 13:30:00     2016-05-04 23:04:00
10  2016-05-04 13:45:00     2016-05-04 22:59:00
11  2016-05-04 14:00:00     2016-05-04 22:59:00
12  2016-05-04 14:15:00     2016-05-04 23:04:00
13  2016-05-04 17:45:00     2016-05-04 21:47:00
14  2016-05-05 23:30:00     2016-05-06 03:25:00
15  2016-05-05 23:45:00     2016-05-06 03:30:00
16  2016-05-06 00:00:00     2016-05-06 03:32:00
17  2016-05-06 00:15:00     2016-05-06 03:31:00
18  2016-05-06 00:30:00     2016-05-06 03:25:00
19  2016-05-06 00:45:00     2016-05-06 02:50:00
20  2016-05-06 01:00:00     2016-05-06 03:25:00

我想只选择条目日期时间在最后选择的结果日期时间之后的行.

例如：第1行的结果时间是“2016-05-02 21:50:00”,因此下一行将是第4行,因为这是输入时间在上次选择的结果时间之后的第一行,下一行假设在第4行的结果时间之后(在“2016-05-03 03:33:00之后”),所以下一行将是第5行.

要求的结果是：

ID  EntryTime               ResultTime
1   2016-05-02 13:30:00     2016-05-02 21:50:00
4   2016-05-03 01:00:00     2016-05-03 03:33:00
5   2016-05-03 10:30:00     2016-05-04 07:47:00
7   2016-05-04 11:30:00     2016-05-05 21:30:00
14  2016-05-05 23:30:00     2016-05-06 03:25:00

解决方法

with cte as (
    select *
    from myTable
    where id = 1
    union all
    select t.*
    from myTable t
    cross join cte
    where t.id = (
        select id 
        from (
            select id,row_number() over (order by id) rn 
            from myTable 
            where entrytime > cte.resulttime) z 
        where rn = 1)
    )
select * from cte;

编辑：对于多个“符号”,这是一个可行的方法(使用示例数据).

DECLARE @myTable TABLE (Symbol CHAR(3),EntryTime DATETIME,ResultTime DATETIME)
INSERT @myTable VALUES ('AAA','2016-05-02 13:30:00','2016-05-02 21:50:00'),('AAA','2016-05-02 14:45:00','2016-05-02 22:00:00'),'2016-05-02 16:30:00','2016-05-02 22:21:00'),'2016-05-03 01:00:00','2016-05-03 03:33:00'),'2016-05-03 10:30:00','2016-05-04 07:47:00'),'2016-05-03 12:30:00','2016-05-03 22:45:00'),'2016-05-04 11:30:00','2016-05-05 21:30:00'),'2016-05-04 12:30:00','2016-05-04 22:58:00'),'2016-05-04 13:30:00','2016-05-04 23:04:00'),'2016-05-04 13:45:00','2016-05-04 22:59:00'),'2016-05-04 14:00:00','2016-05-04 14:15:00','2016-05-04 17:45:00','2016-05-04 21:47:00'),'2016-05-05 23:30:00','2016-05-06 03:25:00'),'2016-05-05 23:45:00','2016-05-06 03:30:00'),'2016-05-06 00:00:00','2016-05-06 03:32:00'),'2016-05-06 00:15:00','2016-05-06 03:31:00'),'2016-05-06 00:30:00','2016-05-06 00:45:00','2016-05-06 02:50:00'),'2016-05-06 01:00:00',('BBB','2016-05-02 01:00:00','2016-05-02 03:01:00'),'2016-05-02 02:00:00','2016-05-02 03:05:00'),'2016-05-02 03:00:00','2016-05-02 03:40:00'),'2016-05-02 04:00:00','2016-05-02 04:01:00'),'2016-05-02 05:00:00','2016-05-03 07:00:00'),'2016-05-02 06:00:00','2016-05-02 07:00:00'),'2016-05-03 06:00:00','2016-05-03 07:05:00'),'2016-05-04 06:01:00','2016-05-04 07:08:00'),'2016-05-04 06:07:00','2016-05-04 07:52:00'),'2016-05-05 06:00:00','2016-05-05 07:49:00'),('CCC','2016-05-05 07:04:00'),'2016-05-05 06:05:00','2016-05-05 06:55:00'),'2016-05-05 07:00:00','2016-05-05 07:10:00'),'2016-05-05 07:06:00','2016-05-05 08:05:00'),'2016-05-05 08:00:00','2016-05-05 08:15:00'),'2016-05-05 08:09:00','2016-05-05 09:00:00');

WITH myTable AS (
    SELECT Symbol,EntryTime,ResultTime,ROW_NUMBER() OVER (PARTITION BY Symbol ORDER BY EntryTime) RN
    FROM @myTable),CTE AS (
    SELECT *
    FROM myTable
    WHERE RN = 1
    UNION ALL
    SELECT T.*
    FROM CTE
    CROSS APPLY (
        SELECT Symbol,RN
        FROM (
            SELECT *,ROW_NUMBER() OVER (ORDER BY EntryTime) RN2
            FROM myTable
            WHERE Symbol = CTE.Symbol
            AND EntryTime > CTE.ResultTime) Z
        WHERE RN2 = 1) T
    )
SELECT Symbol,ResultTime--,RN [ID?]
FROM CTE
ORDER BY Symbol;

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!