一个ORACLE取值判断问题,100求解

Question

近期在做考勤资料分析时,遇到了一个难题,请各位高手帮忙解决!
公司要求做到的分析为:当一个员工同一天内打卡,在10分钟内存在多笔打卡资料时,只保留最早打卡的那一笔.
提供部分数据供各位测试及分析!
 工号             卡机号            上班日期     打卡时间
00000009 09 2009/03/20 07:33
00000009 09 2009/03/20 07:34
00000009 08 2009/03/20 07:45
00000009 09 2009/03/20 12:38
00000009 09 2009/03/20 12:44
00000009 08 2009/03/20 12:55
00000009 09 2009/03/20 17:13
00000009 07 2009/03/20 17:33
00000009 22 2009/03/21 07:34
00000009 19 2009/03/21 07:40
00000009 25 2009/03/23 07:38
00000009 15 2009/03/27 17:47
00000009 07 2009/03/28 07:32
00000009 22 2009/03/28 12:35
00000009 23 2009/03/30 07:31

Answer 1

这些资料已经在同一表里,现在需要进行二次处理.且栏位不是DATE型态

Answer 2

假设你的日期和时间字段都为varchar2型try
select * from tab as a
where not exist (select * from tab where to_date(上班日期||打卡时间,'yyyy/mm/ddhh:mi') 
between to_date(a.上班日期||a.打卡时间,'yyyy/mm/ddhh:mi')-10/60/24 and to_date(a.上班日期||a.打卡时间,'yyyy/mm/ddhh:mi'))

Answer 3

9i以上数据库的话可以用分析函数LAG() over()

Answer 4

按照你所说的方法,现在以下语法进行测试,却报ora-01861错误,请问日期格式在处理时需要注意哪些条件?如何解决?
SELECT A.* FROM 
(
SELECT IC_CARD_ID,IC_CAD_NO,TO_DATE(TIMES,'YYYY-MM-DD HH24:MI:SS') AS TIMES
FROM HR_TEST ORDER BY IC_CARD_ID,TIMES
) A
WHERE NOT EXISTS (SELECT * FROM (
SELECT IC_CARD_ID,IC_CAD_NO,TO_DATE(TIMES,'YYYY-MM-DD HH24:MI:SS') AS TIMES
FROM HR_TEST ORDER BY IC_CARD_ID,TIMES
) B 
WHERE B.TIMES BETWEEN TO_DATE(A.TIMES,'YYYY-MM-DD HH24:MI:SS')-10/24/60 AND TO_DATE(A.TIMES,'YYYY-MM-DD HH24:MI:SS'))

Answer 5

写法是对的，但你的时间没有秒，那你的to_date后面也不能有秒

Answer 6

这个SQL能得到每个人打卡时间最小的结果集
SELECT * FROM 表 t
WHERE NOT EXISTS(
SELECT 1 FROM 表 t1 WHERE t1.工号 = t2.工号 AND t.打卡时间 < t1.打卡时间)

Answer 7

SELECT A.* FROM 
(
SELECT IC_CARD_ID,IC_CAD_NO,TIMES
FROM HR_TEST ORDER BY IC_CARD_ID,TIMES
) A
WHERE NOT EXISTS 
(SELECT * FROM (
SELECT IC_CARD_ID,IC_CAD_NO,TO_DATE(TIMES,'YYYY-MM-DD HH24:MI:SS') AS TIMES
FROM HR_TEST ORDER BY IC_CARD_ID,TIMES
) B 
WHERE A.IC_CARD_ID=B.IC_CARD_ID AND TO_CHAR(B.TIMES,'YYYY-MM-DD HH24:MI:SS') 
BETWEEN TO_CHAR(TO_DATE(A.TIMES,'YYYY-MM-DD HH24:MI:SS')-10/24/60,'YYYY-MM-DD HH24:MI:SS')
    AND TO_CHAR(TO_DATE(A.TIMES,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') AND A.IC_CARD_ID='00000542'
    )
现在这个写法已经写对了,但是会把00000542的资料清掉了哦!
说明在取员工在某一天内某一段时间内(即10分钟内只取一笔资料)还存在问题!

Answer 8

如果注释掉勤 A.IC_CARD_ID='00000542' 这个条件就取不出来值,这是咱回事呢?
实际上我要的就是同一个员工在同一天内打卡资料,如果在10分钟内存在多笔打卡资料时,只保留最早打卡的那一笔.就像下面的这些数据.

Answer 9

TRY过了函数,可不知道如何判断,请高手支招,谢谢!

Answer 10

AND A.IC_CARD_ID='00000542' 这个条件放到外面

Answer 11

已经用lag() over()和lead() over()分析函数得出了结果,只要用循环判断取值即可.
本人也要测试阶段,语法一经验证即结贴.
主要分析函数如下:
SELECT IC_CARD_ID,MAC_ID,TIMES,NEXTTIMES,(NEXTTIMES-TIMES)*1440 SUBVALUES FROM (
SELECT IC_CARD_ID,MAC_ID,TIMES,LEAD(MAC_ID) OVER (PARTITION BY IC_CARD_ID ORDER BY TIMES) NEXTMAC_ID,
LEAD(TIMES) OVER (PARTITION BY IC_CARD_ID ORDER BY TIMES) NEXTTIMES,
LAG(MAC_ID) OVER (PARTITION BY IC_CARD_ID ORDER BY TIMES) PREMAC_ID,
LAG(TIMES) OVER (PARTITION BY IC_CARD_ID ORDER BY TIMES) PRETIMES
FROM HR_WOLF)
工号              机号      打卡时间
00043728 01 2009-3-27 12:30:00
00043728 02 2009-3-27 12:34:00
00043728 03 2009-3-27 12:36:00
00043728 04 2009-3-27 12:56:00
00043728 04 2009-3-27 17:56:00
00043728 04 2009-3-27 17:58:00最后处理结果:
00043728 01 2009-3-27 12:30:00
00043728 04 2009-3-27 12:56:00
00043728 04 2009-3-27 17:56:00

Answer 12

这是sql server 的过程，你改成oracle试试看declare crCard insensitive cursor for 
select TIME1 from kqgrjl
order by TIME1--将间隔十分钟以内的打卡数据过滤
fetch next from crCard into @dktime
while (@@fetch_status<>-1)
begin
  if dateadd(mi,-10,@dktime)<@oldtime 
  begin
    delete from kqgrj where TIME1=@dktime
  end  
  select @oldtime=@dktime           
  fetch next from crCard into @dktime
end
     
close crCard

Answer 13

lag() over()和lead() over()分析函数...学习ing...

Answer 14

试试
select gh,kqjh,cqrq 
from(select gh,kqjh,cqrq,lag(cqrq,1,0) over (order by gh,cqrq) cqrq1
from (select gh,kqjh,to_date(rq||time,'yyyy/mm/ddhh24:mi') cqrq from table order by gh,rq,time))
where cqrq-cqrq1>=10/(24*60);

Answer 15

select IC_CARD_ID,MAC_ID,TIMES 
from (select IC_CARD_ID,MAC_ID,TIMES,lag(TIMES) over (order by IC_CARD_ID,TIMES) TIMES1
from (select IC_CARD_ID,MAC_ID,TIMES from HR_WOLF order by IC_CARD_ID,TIMES))
where TIMES-TIMES1>=10/(24*60);结果如下:
00043728 04 2009-3-27 12:56:00
00043728 04 2009-3-27 17:56:00丢了一条记录:
00043728 01 2009-3-27 12:30:00 
请再测测看哦

Answer 16

再试
select gh,kqjh,cqrq 
from(select gh,kqjh,cqrq,lag(cqrq,1,to_date('11111111','yyyymmdd')) over (order by gh,cqrq) cqrq1
from (select gh,kqjh,to_date(rq||time,'yyyy/mm/ddhh24:mi') cqrq from table order by gh,rq,time))
where cqrq-cqrq1>=10/(24*60);

Answer 17

谢谢,问题已经解决.给出SQL语句和查询结果,马上结贴.
select IC_CARD_ID,MAC_ID,TIMES 
from (select IC_CARD_ID,MAC_ID,TIMES,lag(TIMES,1,to_date('11111111','yyyymmdd')) over (order by IC_CARD_ID,TIMES) TIMES1
from (select IC_CARD_ID,MAC_ID,TIMES from HR_WOLF order by IC_CARD_ID,TIMES))
where TIMES-TIMES1>=10/(24*60);00043728 01 2009-3-27 12:30:00
00043728 04 2009-3-27 12:56:00
00043728 04 2009-3-27 17:56:00