求一oracle语句

2014-03-22 02:14:55 333 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

新手必看！无人机定高PID调参避坑指南：从理论到实战的完整流程本文为无人机新手提供定高PID调参的完整指南，从传感器融合到抗风策略，详细解析多传感器融合、PID控制参数调试及环境干扰应对。通过实战案例和代码示例，帮助开发者快速掌握无人机定高技术，避免常见调参陷阱，提升飞行稳定性。阅读详情

有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号是同一时间打的卡
想了好久，想不到好的方法，求助，多谢帮助

...全文

告别轮询！手把手教你用S32K3的FlexCAN Enhanced FIFO+DMA实现高效CAN FD数据接收本文详细介绍了如何利用S32K3芯片的FlexCAN Enhanced FIFO与DMA组合方案，实现高效CAN FD数据接收，彻底告别传统轮询和中断模式。通过深度缓冲、智能过滤和DMA联动技术，该方案在5Mbps高负载场景下将CPU占用率从42%降至3%以下，显著提升系统实时性和吞吐量。阅读详情

Freeid_shi

Oracle 高级技术 3,499 成员 18,710 内容

+ 关注 + 加入社区

切换为时间正序

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;

经过测试，没有问题。但是发现i h也是这样的。但是这个是全勤情况，考虑缺勤应该也有记录，所以可以满足。

wolfson620 2014-03-27

打赏
举报

引用 2 楼 abin30 的回复:

这样一个思路哈 : 先组装表，得出如下数据打卡人，打卡天，第一天打卡时间，下一天打卡时间，第三天打卡时间然后就可以出来了下面给个思路哈，没有去验证有没有问题如下字段 : 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

思路没问题，但是在reg_date=a.reg_date+1和 reg_date=a.reg_date+2这两处有疑虑，真实环境中的打卡日期并不是连续的，比如周末休息、节假日和旷工情况(系统中具体怎么记录的不太清楚).

wolfson620 2014-03-27

打赏
举报

引用 3 楼 snbxp 的回复:


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语法不太熟悉，不知道写的对不对。

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真心困难。。要求的条件太多了。。

求一oracle语句

相关推荐