c# - 如何参考签入和第二天结帐时间在sql server中获取日期和时间的总工作时间

Question

我有这张桌子，我想得到 time_in 和 time_out

BADGENUMBER CHECKTIME   SENSORID
172 2020-06-16 19:24:49.000 1
172 2020-06-17 05:30:03.000 1
172 2020-06-17 19:41:17.000 1
172 2020-06-18 08:07:51.000 1
172 2020-06-18 19:10:57.000 1
172 2020-06-19 05:25:12.000 1
172 2020-06-20 19:06:06.000 1
172 2020-06-21 05:11:24.000 1
172 2020-06-21 05:11:27.000 1
172 2020-06-21 18:55:04.000 1
172 2020-06-22 06:07:48.000 1
172 2020-06-22 18:34:48.000 1
172 2020-06-23 05:00:31.000 1
172 2020-06-16 19:24:49.000 1
172 2020-06-17 05:30:03.000 1
172 2020-06-17 19:41:17.000 1
172 2020-06-18 08:07:51.000 1
172 2020-06-18 19:10:57.000 1
172 2020-06-19 05:25:12.000 1
172 2020-06-20 19:06:06.000 1
172 2020-06-21 05:11:24.000 1
172 2020-06-21 05:11:27.000 1
172 2020-06-21 18:55:04.000 1
172 2020-06-22 06:07:48.000 1
172 2020-06-22 18:34:48.000 1
172 2020-06-23 05:00:31.000 1
172 2020-06-23 18:57:27.000 1
172 2020-06-23 18:57:29.000 1
172 2020-06-16 19:24:49.000 1
172 2020-06-17 05:30:03.000 1
172 2020-06-17 19:41:17.000 1
172 2020-06-18 08:07:51.000 1
172 2020-06-18 19:10:57.000 1
172 2020-06-19 05:25:12.000 1
172 2020-06-20 19:06:06.000 1
172 2020-06-21 05:11:24.000 1
172 2020-06-21 05:11:27.000 1
172 2020-06-21 18:55:04.000 1
172 2020-06-22 06:07:48.000 1
172 2020-06-22 18:34:48.000 1
172 2020-06-23 05:00:31.000 1
172 2020-06-23 18:57:27.000 1
172 2020-06-23 18:57:29.000 1
172 2020-06-24 07:06:40.000 1
172 2020-06-24 19:01:12.000 1
172 2020-06-24 19:01:20.000 1
172 2020-06-16 19:24:49.000 1
172 2020-06-17 05:30:03.000 1
172 2020-06-17 19:41:17.000 1
172 2020-06-18 08:07:51.000 1
172 2020-06-18 19:10:57.000 1
172 2020-06-19 05:25:12.000 1
172 2020-06-20 19:06:06.000 1
172 2020-06-21 05:11:24.000 1
172 2020-06-21 05:11:27.000 1
172 2020-06-21 18:55:04.000 1
172 2020-06-22 06:07:48.000 1
172 2020-06-22 18:34:48.000 1
172 2020-06-23 05:00:31.000 1
172 2020-06-23 18:57:27.000 1
172 2020-06-23 18:57:29.000 1
172 2020-06-24 07:06:40.000 1
172 2020-06-24 19:01:12.000 1
172 2020-06-24 19:01:20.000 1
172 2020-06-25 07:03:19.000 1
172 2020-06-25 18:53:55.000 1
172 2020-06-26 05:17:45.000 1
172 2020-06-28 19:09:34.000 1
172 2020-06-29 05:00:07.000 1
172 2020-06-29 05:00:09.000 1
172 2020-06-16 19:24:49.000 1
172 2020-06-17 05:30:03.000 1
172 2020-06-17 19:41:17.000 1
172 2020-06-18 08:07:51.000 1
172 2020-06-18 19:10:57.000 1
172 2020-06-19 05:25:12.000 1
172 2020-06-20 19:06:06.000 1
172 2020-06-21 05:11:24.000 1
172 2020-06-21 05:11:27.000 1
172 2020-06-21 18:55:04.000 1
172 2020-06-22 06:07:48.000 1
172 2020-06-22 18:34:48.000 1
172 2020-06-23 05:00:31.000 1
172 2020-06-23 18:57:27.000 1
172 2020-06-23 18:57:29.000 1
172 2020-06-24 07:06:40.000 1
172 2020-06-24 19:01:12.000 1
172 2020-06-24 19:01:20.000 1
172 2020-06-25 07:03:19.000 1
172 2020-06-25 18:53:55.000 1
172 2020-06-26 05:17:45.000 1
172 2020-06-28 19:09:34.000 1
172 2020-06-29 05:00:07.000 1
172 2020-06-29 05:00:09.000 1
172 2020-06-29 19:08:50.000 1
172 2020-06-30 05:23:19.000 1
172 2020-06-30 18:56:33.000 1

如果存在第二天的外出时间，我想得到时间进出

标签： c#sqlsql-server

Answer 1

如果您只想将“奇数行”与“偶数行”结合起来，您可以使用row_number()：

select badgenumber, min(checktime), max(checktime)
from (select t.*, row_number() over (partition by badgenumber order by checktime) as seqnum
      from t
     ) t
group by floor( (seqnum - 1) / 2 );