我正在使用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
解决方法
一种方法是通过使用递归CTE来获取下一行.例如,
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;
