如何查询不同日期的两个关联交易

Question

如果我有一个由两列组成的表，一列用于用户id，另一列用于指纹（考勤、休假），如何检测所有在第一次指纹的第二天（休假时间）的用户？

USERID  CHECKTIME
2982    2015-03-11 09:08:10.000
2982    2015-03-11 20:13:57.000
2982    2015-03-12 08:43:35.000
2982    2015-03-13 04:54:57.000
2982    2015-03-13 05:01:03.000
2982    2015-03-13 19:24:11.000
2982    2015-03-14 10:28:26.000
2982    2015-03-15 04:55:03.000
2982    2015-03-15 05:01:24.000

示例：此用户在08:43:35.000的2015-03-12中进行了检查第二天在CCD_ 4 中的CCD_

我想查询所有有这种情况的用户，考虑到用户可能在同一天有两次以上的入住，所以我只想要第一次和（第二天的最后一次）

Answer 1

假设所有用户都进行了正确的检查，并且没有错过检查时间。

假设我们的架构是：

create table tblchk (USERID int,  CHECKTIME datetime)
insert into tblchk values
(2982    ,'2015-03-11 09:08:10.000'),
(2982    ,'2015-03-11 20:13:57.000'),
(2982    ,'2015-03-12 08:43:35.000'),
(2982    ,'2015-03-13 04:54:57.000'),
(2982    ,'2015-03-13 05:01:03.000'),
(2982    ,'2015-03-13 19:24:11.000'),
(2982    ,'2015-03-14 10:28:26.000'),
(2982    ,'2015-03-15 04:55:03.000'),
(2982    ,'2015-03-15 05:01:24.000');

不可以，我们可以使用row number来指定哪个记录用于enter time，哪个是exit time，ODD s为输入，Even s为退出：

select userid,
       checktime,
       case row_number()over(partition by userid order by checktime)%2
            when 1 then 'Enter'
            else 'Exit'
       end [type]
from tblchk

结果是：

userid  checktime               type
2982    2015-03-11 09:08:10.000 Enter
2982    2015-03-11 20:13:57.000 Exit
2982    2015-03-12 08:43:35.000 Enter
2982    2015-03-13 04:54:57.000 Exit
2982    2015-03-13 05:01:03.000 Enter
2982    2015-03-13 19:24:11.000 Exit
2982    2015-03-14 10:28:26.000 Enter
2982    2015-03-15 04:55:03.000 Exit
2982    2015-03-15 05:01:24.000 Enter

现在，如果我们想指定最后一次检查时间为enter，第二天的第一次检查时间是exit的日期，我们可以使用上面的查询作为：

with cte as
(
    select userid,
           checktime,
           row_number()over(partition by userid order by checktime) rn
    from tblchk
)
select q_enter.userid as [USER],
       q_enter.checktime as EnterTime, 
       q_exit.checktime as ExitTime
from
(
    select userid,
           max(checktime) checktime,
           max(rn) rn
    from cte 
    group by userid,cast(checktime as date)
    having max(rn)%2=1
)q_enter
join 
(
    select userid,
           min(checktime) checktime,
           min(rn) rn
    from cte 
    group by userid,cast(checktime as date)
    having min(rn)%2=0
)q_exit
on q_enter.userid=q_exit.userid and q_enter.rn=q_exit.rn-1

结果：

USER    EnterTime               ExitTime
2982    2015-03-12 08:43:35.000 2015-03-13 04:54:57.000
2982    2015-03-14 10:28:26.000 2015-03-15 04:55:03.000

Answer 2

您可以使用聚合函数，如min和max，它提供先进先出值

你可以在这里查一下http://sqlfiddle.com/#！9/68897/2

按用户ID从出席组中选择用户ID、最小值（CHECKTIME）、最大值（CHECKTIME）