表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熱心網友回復：
CREATE TABLE A(
AttendanceID VARCHAR(50) ,
SchedulingID VARCHAR(50) ,
AttendanceBeginTime DATETIME ,
AttendanceEndTime DATETIME
)
CREATE TABLE S
(
SchedulingID VARCHAR(50) ,
SchedulingName VARCHAR(50) ,
SchedulingBeginTime VARCHAR(50) ,
SchedulingEndTime VARCHAR(50)
)
INSERT INTO dbo.A
SELECT '12-7572TMP00000000' ,
'12-7572TMP00000015' ,
'2011-04-18 07:26:46.767' ,
NULL
UNION ALL
SELECT '12-7572TMP00000001' ,
'12-7572TMP00000016' ,
'2011-04-18 15:26:46.767' ,
'2011-04-18 22:26:46.767'
UNION ALL
SELECT '12-7572TMP00000002' ,
'12-7572TMP00000016' ,
'2011-04-19 14:26:46.767' ,
'2011-04-19 22:12:46.767'
UNION ALL
SELECT '12-7572TMP00000003' ,
'12-7572TMP00000015' ,
'2011-04-20 07:56:46.767' ,
'2011-04-20 13:56:46.767'
INSERT INTO S
SELECT '12-7572TMP00000015' ,
'早班' ,
'08:00:00' ,
'14:00:00'
UNION ALL
SELECT '12-7572TMP00000016' ,
'中班' ,
'15:00:00' ,
'22:00:00'
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/94907.html
標籤：開發
上一篇：ubuntu occi client連接error tns:ORA-12163
下一篇：求個資料庫復雜錯誤資料的處理