我有一个特定用户在特定工作站上做某事的时刻列表：
station timestamp
TC61879 2016-03-25 09:34:40.000
TC61879 2016-03-25 09:38:36.000
TC61879 2016-03-25 10:01:17.000
TC61879 2016-03-25 10:02:10.000
TC61879 2016-03-25 10:04:01.000
TC61879 2016-03-25 10:04:43.000
TC61879 2016-03-25 10:05:49.000
TC61879 2016-03-25 10:06:00.000
TC61878 2016-03-25 10:08:05.000*
TC61879 2016-03-25 10:09:41.000
TC61879 2016-03-25 10:10:40.000
TC61879 2016-03-25 10:35:50.000
TC61879 2016-03-25 10:37:57.000
TC61879 2016-03-25 10:38:21.000
TC61879 2016-03-25 10:39:34.000
TC61879 2016-03-25 10:40:59.000
TC61879 2016-03-25 10:41:20.000
TC61879 2016-03-25 10:42:21.000
TC61879 2016-03-25 10:44:05.000
TC61879 2016-03-25 10:44:17.000
TC61879 2016-03-25 10:46:25.000
TC61879 2016-03-25 10:47:48.000
TC61879 2016-03-25 10:49:03.000
TC61879 2016-03-25 10:51:31.000
TC61879 2016-03-25 10:51:58.000
TC61875 2016-03-25 10:52:42.000*
TC61875 2016-03-25 10:53:49.000*
TC61879 2016-03-25 10:53:57.000
TC61879 2016-03-25 11:36:58.000
TC61879 2016-03-25 11:37:37.000
TC61879 2016-03-25 11:38:45.000
TC61879 2016-03-25 11:40:08.000
TC61879 2016-03-25 11:41:46.000
TC61879 2016-03-25 11:43:43.000
TC61879 2016-03-25 11:44:49.000
TC61879 2016-03-25 11:46:06.000
TC61879 2016-03-25 11:48:17.000
TC61879 2016-03-25 11:50:22.000
TC61879 2016-03-25 11:52:06.000
TC61879 2016-03-25 11:52:22.000
TC61879 2016-03-25 11:53:07.000
TC61879 2016-03-25 11:55:29.000
TC61879 2016-03-25 11:55:49.000
TC61879 2016-03-25 11:56:24.000
TC61879 2016-03-25 11:57:20.000
TC61879 2016-03-25 11:57:55.000
TC61879 2016-03-25 11:58:15.000
TC61879 2016-03-25 11:59:05.000
TC61879 2016-03-25 11:59:17.000
TC61878 2016-03-25 11:59:52.000*
TC61879 2016-03-25 12:00:08.000
TC61879 2016-03-25 12:01:04.000
TC61879 2016-03-25 12:01:36.000
TC61878 2016-03-25 12:04:42.000*
TC61879 2016-03-25 12:04:46.000
TC61879 2016-03-25 12:05:37.000
TC61878 2016-03-25 12:07:37.000*
TC61879 2016-03-25 12:07:47.000
TC61879 2016-03-25 12:08:36.000
TC61879 2016-03-25 12:10:16.000
TC61879 2016-03-25 12:11:30.000
星号是指用户在与默认工作站不同的工作站上执行某些操作。需要的是每个工作站从到的列表，如下所示：
Station From To
TC61879 2016-03-25 09:34:40.000 2016-03-25 10:08:05.000
TC61878 2016-03-25 10:08:05.000 2016-03-25 10:09:41.000
TC61879 2016-03-25 10:09:41.000 2016-03-25 10:52:42.000
TC61875 2016-03-25 10:52:42.000 2016-03-25 10:53:57.000
TC61879 2016-03-25 10:53:57.000 2016-03-25 11:59:52.000
TC61878 2016-03-25 11:59:52.000 2016-03-25 12:00:08.000
TC61879 2016-03-25 12:00:08.000 2016-03-25 12:04:42.000
TC61878 2016-03-25 12:04:42.000 2016-03-25 12:04:46.000
TC61879 2016-03-25 12:04:46.000 2016-03-25 12:07:37.000
TC61878 2016-03-25 12:07:37.000 2016-03-25 12:07:47.000
TC61879 2016-03-25 12:07:47.000 2016-03-25 12:11:30.000
我尝试过使用窗口函数和联接的各种方法，但我一直遇到这样的问题，即当用户从一个工作站切换到另一个工作站并返回时，数据无法反映用户在另一个工位上做了什么。
select station from resulttable where @datetime between from and to
此查询应该只给出一行作为结果
最终，我想使用这些数据来确定用户在任何给定日期时间的位置。
我应该如何处理？
您希望根据列中的更改分配组（按时间排序）。有一个使用行号差异的"技巧"使这变得非常容易：
select station, min(timestamp), max(timestamp)
from (select t.*,
row_number() over (order by timestamp) as seqnum_t,
row_number() over (partition by station order by timestamp) as seqnum_wst
from t
) t
group by (seqnum_t - seqnum_wst), station;
如果您查看生成的序列号，第一个是枚举行。第二个枚举每个工作站内的行。如果行是连续的，则差值是恒定的。