需求描述:当前项目需要查询员工上班期间出入公司大门的记录,且计算出外出的时间
取数时相关问题:1、每次刷卡,可能会刷多次才能打开门;2、尽量规范出入都刷自己的卡并且都刷卡(避免出去刷自己的,入公司时蹭别人卡或者没刷卡,这样系统会认为出去未归)
最开始的思路是:连续的入、出分组,上面数据就是分6组,入则取组内最大的刷卡时间,出则取组内最小的刷卡时间;但是这样不好分组,所以换了一种思路:与下一条记录对比,用到统计函数:Lead和lag,分别是获取下一行和前一行的记录
sql:
SELECT T1.*,
LEAD(T1.INOROUT) OVER(ORDER BY T1.BRUSHCARDTIME) INOROUT_NEXT,--获取下一行
LAG(T1.INOROUT) OVER(ORDER BY T1.BRUSHCARDTIME) INOROUT_PRE--获取前一行
FROM OA_CARD_RECORD_DMJL_TEMP T1,HR_HUMANARCHIVE T2
WHERE T1.EMPLOYEENO = T2.EMPLOYEENO
AND T2.ARCHIVEID = 876869211
and t1.brushcarddate = to_date('2018-06-18', 'yyyy-mm-dd')
这样要取连续入的最大刷卡时间,则INOROUT='入' 时,取与下一条数据INOROUT的不一致的记录,即INOROUT <> NVL(INOROUT_NEXT,'0');要取连续出的最小的刷卡时间,则INOROUT='出' 时,取与上一条数据INOROUT的不一致的记录,即INOROUT <> NVL(INOROUT_PRE,'0');sql与结果如下
SELECT AA.*,AA.BRUSHCARDTIME LAST_TIME
FROM (
SELECT T1.*,
LEAD(T1.INOROUT) OVER(ORDER BY T1.BRUSHCARDTIME) INOROUT_NEXT,--获取下一行
LAG(T1.INOROUT) OVER(ORDER BY T1.BRUSHCARDTIME) INOROUT_PRE--获取前一行
FROM OA_CARD_RECORD_DMJL_TEMP T1,HR_HUMANARCHIVE T2
WHERE T1.EMPLOYEENO = T2.EMPLOYEENO
AND T2.ARCHIVEID = 876869211
and t1.brushcarddate = to_date('2018-06-18', 'yyyy-mm-dd')
) AA
WHERE (AA.INOROUT = '入' AND AA.INOROUT <> NVL(AA.INOROUT_NEXT,'0'))
OR (AA.INOROUT = '出' AND AA.INOROUT <> NVL(AA.INOROUT_PRE,'0'))
ORDER BY AA.BRUSHCARDTIME
收藏一个相关文档:https://blog.csdn.net/danielinbiti/article/details/42265023