这样一个思路哈 : 先组装表,得出如下数据 打卡人,打卡天,第一天打卡时间,下一天打卡时间,第三天打卡时间 然后就可以出来了下面给个思路哈, 没有去验证 有没有问题如下 字段 :user,reg_date,reg_time select distinct user from (select a.*, (select reg_time from t_reg where reg_date=a.reg_date+1 and user=a.user) as reg_time_2 (select reg_time from t_reg where reg_date=a.reg_date+2 and user=a.user) as reg_time_3 from t_reg a ) t where reg_time =reg_time2 and reg_time=reg_time_3
WITH ta AS (select 1 rq,'7:00' sj, 'A、B、D' dkr from dual union all select 1 rq,'7:10' sj, 'D、F' dkr from dual union all select 1 rq,'7:11' sj, 'G、H、I、J、K、M、N' dkr from dual union all select 2 rq,'7:05' sj, 'C、D' dkr from dual union all select 2 rq,'7:17' sj, 'A、E、F' dkr from dual union all select 2 rq,'7:22' sj, 'G、H、I' dkr from dual union all select 2 rq,'7:23' sj, 'J、K、M、N' dkr from dual union all select 3 rq,'7:05' sj, 'A、F' dkr from dual union all select 3 rq,'7:19' sj, 'C、E、F' dkr from dual union all select 3 rq,'7:24' sj, 'B、H、I' dkr from dual union all select 3 rq,'7:26' sj, 'G、M、N' dkr from dual union all select 5 rq,'7:25' sj, 'M、N、F' dkr from dual union all select 5 rq,'7:02' sj, 'G、E、F' dkr from dual union all select 5 rq,'7:14' sj, 'A、B、H、I' dkr from dual union all select 5 rq,'7:16' sj, 'C、J' dkr from dual ), tb AS (select 'A' name from dual union all select 'B' name from dual union all select 'C' name from dual union all select 'D' name from dual union all select 'E' name from dual union all select 'F' name from dual union all select 'G' name from dual union all select 'H' name from dual union all select 'I' name from dual union all select 'J' name from dual union all select 'K' name from dual union all select 'L' name from dual union all select 'M' name from dual union all select 'N' name from dual union all select 'O' name from dual union all select 'P' name from dual union all select 'Q' name from dual union all select 'R' name from dual union all select 'S' name from dual union all select 'T' name from dual union all select 'U' name from dual union all select 'V' name from dual union all select 'W' name from dual union all select 'X' name from dual union all select 'Y' name from dual ), WITH TC AS (SELECT A.NAME P1, B.NAME P2 FROM TB A, TB B WHERE A.NAME < B.NAME), TD AS (SELECT * FROM TA, TC WHERE REGEXP_LIKE(DKR, P1) AND REGEXP_LIKE(DKR, P2)), TE AS (SELECT P1, P2, RQ, LEAD(RQ, 1) OVER(PARTITION BY P1, P2 ORDER BY RQ) RQ1, LEAD(RQ, 2) OVER(PARTITION BY P1, P2 ORDER BY RQ) RQ2 FROM TD)
SELECT P1, P2, RQ, RQ1, RQ2 FROM TE WHERE RQ1 = RQ + 1 AND RQ2 = RQ1 + 1 ------------- --结果 P1 P2 RQ RQ1 RQ2 H I 1 2 3 M N 1 2 3
1. WITH TC AS (SELECT A.NAME P1, B.NAME P2 FROM TB A, TB B WHERE A.NAME < B.NAME), 这一步处理是为了减少后面笛卡尔积的计算量么,如果当天只有一个人上班打卡,那么这条记录就会漏掉。2.用正则表达式获取每个人的打卡情况,这个得点32个赞。3.楼主的需求是“连续3天打卡时间一样”,TE AS (SELECT P1, P2, RQ, LEAD(RQ, 1) OVER(PARTITION BY P1, P2 ORDER BY RQ) RQ1, LEAD(RQ, 2) OVER(PARTITION BY P1, P2 ORDER BY RQ) RQ2 FROM TD) 此处应选SJ字段,而不是日期。WITH ta AS (select 1 rq,'7:00' sj, 'A、B、D' dkr from dual union all select 1 rq,'7:10' sj, 'D、F' dkr from dual union all select 1 rq,'7:11' sj, 'G、H、I、J、K、M、N' dkr from dual union all select 2 rq,'7:05' sj, 'C、D' dkr from dual union all select 2 rq,'7:17' sj, 'A、E、F' dkr from dual union all select 2 rq,'7:22' sj, 'G、H、I' dkr from dual union all select 2 rq,'7:23' sj, 'J、K、M、N' dkr from dual union all select 3 rq,'7:05' sj, 'A、F' dkr from dual union all select 3 rq,'7:19' sj, 'C、E、F' dkr from dual union all select 3 rq,'7:24' sj, 'B、H、I' dkr from dual union all select 3 rq,'7:26' sj, 'G、M、N' dkr from dual union all select 5 rq,'7:25' sj, 'M、N、F' dkr from dual union all select 5 rq,'7:02' sj, 'G、E、F' dkr from dual union all select 5 rq,'7:14' sj, 'A、B、H、I' dkr from dual union all select 5 rq,'7:16' sj, 'C、J' dkr from dual ), tb AS (select 'A' name from dual union all select 'B' name from dual union all select 'C' name from dual union all select 'D' name from dual union all select 'E' name from dual union all select 'F' name from dual union all select 'G' name from dual union all select 'H' name from dual union all select 'I' name from dual union all select 'J' name from dual union all select 'K' name from dual union all select 'L' name from dual union all select 'M' name from dual union all select 'N' name from dual union all select 'O' name from dual union all select 'P' name from dual union all select 'Q' name from dual union all select 'R' name from dual union all select 'S' name from dual union all select 'T' name from dual union all select 'U' name from dual union all select 'V' name from dual union all select 'W' name from dual union all select 'X' name from dual union all select 'Y' name from dual ), WITH TD AS (SELECT NAME,RQ,SJ FROM TA, TC WHERE REGEXP_LIKE(DKR, NAME) ORDER BY NAME,RQ), TE AS (SELECT NAME, RQ, SJ, LEAD(SJ, 1) OVER(PARTITION BY NAME ORDER BY RQ) SJ1, LEAD(SJ, 2) OVER(PARTITION BY NAME ORDER BY RQ) SJ2 FROM TD)
SELECT * FROM TE WHERE SJ=SJ1 AND SJ=SJ2; ORACLE语法不太熟悉,不知道写的对不对。
with temp_tab as ( select a.ctr, a.cdate, lead(cdate, 1) over(partition by ctr order by cdate desc) cdate1, lead(cdate, 2) over(partition by ctr order by cdate) cdate2 from temp_shen a ) select * from temp_tab c,temp_tab b where c.cdate=b.cdate and c.cdate1=b.cdate1 and c.cdate2=b.cdate2 and c.ctr<>b.ctr; 经过测试,没有问题。但是发现i h也是这样的。但是这个是全勤情况,考虑缺勤应该也有记录,所以可以满足。
打卡人,打卡天,第一天打卡时间,下一天打卡时间,第三天打卡时间 然后就可以出来了下面给个思路哈, 没有去验证 有没有问题如下 字段 :user,reg_date,reg_time
select distinct user from
(select a.*,
(select reg_time from t_reg where reg_date=a.reg_date+1 and user=a.user) as reg_time_2
(select reg_time from t_reg where reg_date=a.reg_date+2 and user=a.user) as reg_time_3
from t_reg a ) t where reg_time =reg_time2 and reg_time=reg_time_3
WITH ta AS
(select 1 rq,'7:00' sj, 'A、B、D' dkr from dual union all
select 1 rq,'7:10' sj, 'D、F' dkr from dual union all
select 1 rq,'7:11' sj, 'G、H、I、J、K、M、N' dkr from dual union all
select 2 rq,'7:05' sj, 'C、D' dkr from dual union all
select 2 rq,'7:17' sj, 'A、E、F' dkr from dual union all
select 2 rq,'7:22' sj, 'G、H、I' dkr from dual union all
select 2 rq,'7:23' sj, 'J、K、M、N' dkr from dual union all
select 3 rq,'7:05' sj, 'A、F' dkr from dual union all
select 3 rq,'7:19' sj, 'C、E、F' dkr from dual union all
select 3 rq,'7:24' sj, 'B、H、I' dkr from dual union all
select 3 rq,'7:26' sj, 'G、M、N' dkr from dual union all
select 5 rq,'7:25' sj, 'M、N、F' dkr from dual union all
select 5 rq,'7:02' sj, 'G、E、F' dkr from dual union all
select 5 rq,'7:14' sj, 'A、B、H、I' dkr from dual union all
select 5 rq,'7:16' sj, 'C、J' dkr from dual
),
tb AS
(select 'A' name from dual union all
select 'B' name from dual union all
select 'C' name from dual union all
select 'D' name from dual union all
select 'E' name from dual union all
select 'F' name from dual union all
select 'G' name from dual union all
select 'H' name from dual union all
select 'I' name from dual union all
select 'J' name from dual union all
select 'K' name from dual union all
select 'L' name from dual union all
select 'M' name from dual union all
select 'N' name from dual union all
select 'O' name from dual union all
select 'P' name from dual union all
select 'Q' name from dual union all
select 'R' name from dual union all
select 'S' name from dual union all
select 'T' name from dual union all
select 'U' name from dual union all
select 'V' name from dual union all
select 'W' name from dual union all
select 'X' name from dual union all
select 'Y' name from dual
),
WITH TC AS
(SELECT A.NAME P1, B.NAME P2 FROM TB A, TB B WHERE A.NAME < B.NAME),
TD AS
(SELECT *
FROM TA, TC
WHERE REGEXP_LIKE(DKR, P1)
AND REGEXP_LIKE(DKR, P2)),
TE AS
(SELECT P1,
P2,
RQ,
LEAD(RQ, 1) OVER(PARTITION BY P1, P2 ORDER BY RQ) RQ1,
LEAD(RQ, 2) OVER(PARTITION BY P1, P2 ORDER BY RQ) RQ2
FROM TD)
SELECT P1, P2, RQ, RQ1, RQ2
FROM TE
WHERE RQ1 = RQ + 1
AND RQ2 = RQ1 + 1
-------------
--结果
P1 P2 RQ RQ1 RQ2
H I 1 2 3
M N 1 2 3
1. WITH TC AS
(SELECT A.NAME P1, B.NAME P2 FROM TB A, TB B WHERE A.NAME < B.NAME), 这一步处理是为了减少后面笛卡尔积的计算量么,如果当天只有一个人上班打卡,那么这条记录就会漏掉。2.用正则表达式获取每个人的打卡情况,这个得点32个赞。3.楼主的需求是“连续3天打卡时间一样”,TE AS
(SELECT P1,
P2,
RQ,
LEAD(RQ, 1) OVER(PARTITION BY P1, P2 ORDER BY RQ) RQ1,
LEAD(RQ, 2) OVER(PARTITION BY P1, P2 ORDER BY RQ) RQ2
FROM TD)
此处应选SJ字段,而不是日期。WITH ta AS
(select 1 rq,'7:00' sj, 'A、B、D' dkr from dual union all
select 1 rq,'7:10' sj, 'D、F' dkr from dual union all
select 1 rq,'7:11' sj, 'G、H、I、J、K、M、N' dkr from dual union all
select 2 rq,'7:05' sj, 'C、D' dkr from dual union all
select 2 rq,'7:17' sj, 'A、E、F' dkr from dual union all
select 2 rq,'7:22' sj, 'G、H、I' dkr from dual union all
select 2 rq,'7:23' sj, 'J、K、M、N' dkr from dual union all
select 3 rq,'7:05' sj, 'A、F' dkr from dual union all
select 3 rq,'7:19' sj, 'C、E、F' dkr from dual union all
select 3 rq,'7:24' sj, 'B、H、I' dkr from dual union all
select 3 rq,'7:26' sj, 'G、M、N' dkr from dual union all
select 5 rq,'7:25' sj, 'M、N、F' dkr from dual union all
select 5 rq,'7:02' sj, 'G、E、F' dkr from dual union all
select 5 rq,'7:14' sj, 'A、B、H、I' dkr from dual union all
select 5 rq,'7:16' sj, 'C、J' dkr from dual
),
tb AS
(select 'A' name from dual union all
select 'B' name from dual union all
select 'C' name from dual union all
select 'D' name from dual union all
select 'E' name from dual union all
select 'F' name from dual union all
select 'G' name from dual union all
select 'H' name from dual union all
select 'I' name from dual union all
select 'J' name from dual union all
select 'K' name from dual union all
select 'L' name from dual union all
select 'M' name from dual union all
select 'N' name from dual union all
select 'O' name from dual union all
select 'P' name from dual union all
select 'Q' name from dual union all
select 'R' name from dual union all
select 'S' name from dual union all
select 'T' name from dual union all
select 'U' name from dual union all
select 'V' name from dual union all
select 'W' name from dual union all
select 'X' name from dual union all
select 'Y' name from dual
),
WITH
TD AS
(SELECT NAME,RQ,SJ
FROM TA, TC
WHERE REGEXP_LIKE(DKR, NAME)
ORDER BY NAME,RQ),
TE AS
(SELECT NAME,
RQ,
SJ,
LEAD(SJ, 1) OVER(PARTITION BY NAME ORDER BY RQ) SJ1,
LEAD(SJ, 2) OVER(PARTITION BY NAME ORDER BY RQ) SJ2
FROM TD)
SELECT *
FROM TE
WHERE SJ=SJ1
AND SJ=SJ2;
ORACLE语法不太熟悉,不知道写的对不对。
select a.ctr,
a.cdate,
lead(cdate, 1) over(partition by ctr order by cdate desc) cdate1,
lead(cdate, 2) over(partition by ctr order by cdate) cdate2
from temp_shen a )
select * from temp_tab c,temp_tab b
where c.cdate=b.cdate
and c.cdate1=b.cdate1
and c.cdate2=b.cdate2
and c.ctr<>b.ctr;
经过测试,没有问题。但是发现i h也是这样的。但是这个是全勤情况,考虑缺勤应该也有记录,所以可以满足。