3,491
社区成员
发帖
与我相关
我的任务
分享求一oracle语句
有N条记录，记录打卡人，打卡时间。现在要找到不同的人连续3天打卡时间一样的记录
如1号 7:00 打卡人A、B、D
7:10 打卡人D、F
7:11 打卡人G、H、I、J、K、M、N
…………
2号 7:05 打卡人C、D
7:17 打卡人A、E、F
7:22 打卡人G、H、I
7:23 打卡人 J、K、M、N
…………
3号 7:05 打卡人A、F
7:19 打卡人C、E、F
7:24 打卡人B、H、I
7:26 打卡人 G、M、N
…………
5号 7:25 打卡人M、N、F
7:02 打卡人G、E、F
7:14 打卡人A、B、H、I
7:16 打卡人 C、J
…………
求一oracle语句，查询到不同人连续3天或以上打卡时间相同的记录
即能找到M、N二个人在1、2、3号是同一时间打的卡
想了好久，想不到好的方法，求助，多谢帮助
如1号 7:00 打卡人A、B、D
7:10 打卡人D、F
7:11 打卡人G、H、I、J、K、M、N
…………
2号 7:05 打卡人C、D
7:17 打卡人A、E、F
7:22 打卡人G、H、I
7:23 打卡人 J、K、M、N
…………
3号 7:05 打卡人A、F
7:19 打卡人C、E、F
7:24 打卡人B、H、I
7:26 打卡人 G、M、N
…………
5号 7:25 打卡人M、N、F
7:02 打卡人G、E、F
7:14 打卡人A、B、H、I
7:16 打卡人 C、J
…………
求一oracle语句，查询到不同人连续3天或以上打卡时间相同的记录
即能找到M、N二个人在1、2、3号是同一时间打的卡
想了好久，想不到好的方法，求助，多谢帮助
...全文
请发表友善的回复…
发表回复
Freeid_shi 2014-04-14
- 打赏
- 举报
多谢各位热心相助
shenlele088 2014-04-01
- 打赏
- 举报
第二个缺个desc
shenlele088 2014-04-01
- 打赏
- 举报
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;wolfson620 2014-03-27
- 打赏
- 举报
思路没问题，但是在reg_date=a.reg_date+1和 reg_date=a.reg_date+2这两处有疑虑，真实环境中的打卡日期并不是连续的，比如周末休息、节假日和旷工情况(系统中具体怎么记录的不太清楚).
wolfson620 2014-03-27
- 打赏
- 举报
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;
snbxp 2014-03-24
- 打赏
- 举报
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
abin30 2014-03-23
- 打赏
- 举报
这样一个思路哈 : 先组装表，得出如下数据
打卡人，打卡天，第一天打卡时间，下一天打卡时间，第三天打卡时间 然后就可以出来了
下面给个思路哈， 没有去验证 有没有问题
如下 字段 :
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
流浪川 2014-03-22
- 打赏
- 举报
这个用sql真心困难。。要求的条件太多了。。
