表A：（考勤記錄）
AttendanceID SchedulingID AttendanceBeginTime AttendanceEndTime
12-7572TMP00000000 12-7572TMP00000015 2011-04-18 07:26:46.767 NULL
12-7572TMP00000001 12-7572TMP00000016 2011-04-18 15:26:46.767 2011-04-18 22:26:46.767
12-7572TMP00000002 12-7572TMP00000016 2011-04-19 14:26:46.767 2011-04-19 22:12:46.767
12-7572TMP00000003 12-7572TMP00000015 2011-04-20 07:56:46.767 2011-04-20 13:56:46.767
表S：（班次資訊）
SchedulingID SchedulingName SchedulingBeginTime SchedulingEndTime
12-7572TMP00000015 早班 08:00:00 14:00:00
12-7572TMP00000016 中班 15:00:00 22:00:00
我想根據以上兩張表得到以下資訊：（獲取遲到早退的時間）
AttendanceID SchedulingName 遲到時間 早退時間
12-7572TMP00000000 早班 無 未打卡
12-7572TMP00000001 中班 00:26:46 無
12-7572TMP00000002 中班 無 無
12-7572TMP00000003 早班 無 00:03:14
uj5u.com熱心網友回復：
oraclecreate table A
(
attendanceid VARCHAR2(50),
schedulingid VARCHAR2(50),
attendancebegintime VARCHAR2(50),
attendanceendtime VARCHAR2(50)
);
create table S
(
schedulingid VARCHAR2(50),
schedulingname VARCHAR2(50),
schedulingbegintime VARCHAR2(50),
schedulingendtime VARCHAR2(50)
);
insert into a (ATTENDANCEID, SCHEDULINGID, ATTENDANCEBEGINTIME, ATTENDANCEENDTIME)
values ('12-7572TMP00000000', '12-7572TMP00000015', '2011-04-18 07:26:46.767', null);
insert into a (ATTENDANCEID, SCHEDULINGID, ATTENDANCEBEGINTIME, ATTENDANCEENDTIME)
values ('12-7572TMP00000001', '12-7572TMP00000016', '2011-04-18 15:26:46.767', '2011-04-18 22:26:46.767');
insert into a (ATTENDANCEID, SCHEDULINGID, ATTENDANCEBEGINTIME, ATTENDANCEENDTIME)
values ('12-7572TMP00000002', '12-7572TMP00000016', '2011-04-19 14:26:46.767', '2011-04-19 22:12:46.767');
insert into a (ATTENDANCEID, SCHEDULINGID, ATTENDANCEBEGINTIME, ATTENDANCEENDTIME)
values ('12-7572TMP00000003', '12-7572TMP00000015', '2011-04-20 07:56:46.767', '2011-04-20 13:56:46.767');
;
insert into s (SCHEDULINGID, SCHEDULINGNAME, SCHEDULINGBEGINTIME, SCHEDULINGENDTIME)
values ('12-7572TMP00000015', '早班', '08:00:00', '14:00:00');
insert into s (SCHEDULINGID, SCHEDULINGNAME, SCHEDULINGBEGINTIME, SCHEDULINGENDTIME)
values ('12-7572TMP00000016', '中班', '15:00:00', '22:00:00');
uj5u.com熱心網友回復：
select a.id ,b.name ,case when a.time<=b.time THEN '無' when a.time is null THEN '未打卡' else a.time-b.time END "遲到時間" FROM t1 a left join t2 b on a.id=b.id大概這樣 ，馬上下班了 ，時間的轉換自己搞下
uj5u.com熱心網友回復：
select a.attendanceid,
s.schedulingname,
substr(a.attendancebegintime,12,8) ,
case when substr(a.attendancebegintime,12,8) <= s.schedulingbegintime then '無'
when a.attendancebegintime is null then '未打卡'
else to_char(numtodsinterval(to_date(substr(a.attendancebegintime,12,8),'hh24:mi:ss') - to_date(s.schedulingbegintime,'hh24:mi:ss'),'DAY'))
end,
case when substr(a.attendanceendtime,12,8) >= s.schedulingendtime then '無'
when a.attendanceendtime is null then '未打卡'
else to_char(numtodsinterval(to_date(s.schedulingendtime,'hh24:mi:ss') - to_date(substr(a.attendanceendtime,12,8),'hh24:mi:ss'),'DAY'))
end
from a
left join s
on a.schedulingid = s.schedulingid
order by a.attendanceid
uj5u.com熱心網友回復：
寫幾個 case when 應該能解決問題轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/93516.html
標籤：開發