（有一定難度）求mysql 的sql語句，員工某段上下班時間忘記打卡，如何將漏打的內容補上？

[cerwg]

某家工廠規定了員工上下班時間都必須打卡，包括上午、下午、晚上的工作段，每天正常工作情況下要打6次卡，現某完工一天之內只打了兩次卡（開始和結束時間），如何用盡可能高效的sql語句將該員工漏打的內容補上？

詳細內容如下：
-------------------------------

表結構

task {
e_id 員工編號
bt 開始時間
et 結束時間
}

setting
{
e_id 員工時間

b1 上午工作開始時間
e1 上午工作結束時間

b2 下午工作開始時間，e1 -> b2 為中午休息時間
e2 下午工作結束時間

b3 晚上工作開始時間，e2 -> b3 為傍晚休息時間
e3 晚上工作結束時間
}

由於某些原因，task表中部分數據的時間段被錄入為跨段（例如上午至下午，或者全天）時間，含休息時間，例如

task（表） :
e_id =1,bt = 8:00, et = 20:00 （包含了上午、下午的休息時間）

setting（表）:
e_id =1
b1 = 7:00 , e1 = 11:30
b2 = 13:00 , e2 = 17:00
b3 = 18:00 , e3 = 22:00

現希望以setting表為標准，將員工完成任務（task）的時間（bt,et），扣除相應的休息時間，將“跨段”的時間分拆為新的工作時間。

上例員工記號1的task更新後為
e_id =1 , bt = 【8:00】 , et = 11:30
e_id =1 , bt = 13:00, et = 17:00
e_id =1 , bt = 18:00, et = 【20:00】

【】符號為本題提示信息。

[懒得去死]

mysql> create table task (
-> e_id int not null,
-> bt int,
-> et int
-> );
Query OK, 0 rows affected (0.19 sec)

mysql> create table setting (
-> e_id int not null primary
-> b1 int,
-> e1 int,
-> b2 int,
-> e2 int,
-> b3 int,
-> e3 int
-> );
Query OK, 0 rows affected (0.17 sec)
mysql> insert into task values(1,8,20);
Query OK, 1 row affected (0.17 sec)
mysql> insert into setting values(1,7,11,13,17,18,22);
Query OK, 1 row affected (0.05 sec)

mysql> create table task_backup select * from task;
Query OK, 1 row affected (0.11 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> truncate table task;
Query OK, 1 row affected (0.03 sec)

mysql> insert into task select e_id,b1,e1 from setting union all select e_id,b2,e2 from setting union all select e_id,b3,e3 from setting;
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from task;
+------+------+------+
| e_id | bt | et |
+------+------+------+
| 1 | 7 | 11 |
| 1 | 13 | 17 |
| 1 | 18 | 22 |
+------+------+------+
3 rows in set (0.00 sec)

mysql> select bt,et from task_backup where e_id = 1 into @bt,@et;
Query OK, 1 row affected (0.00 sec)

mysql> update task set bt = @bt where bt < @bt;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> update task set et = @et where et > @et;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from task;
+------+------+------+
| e_id | bt | et |
+------+------+------+
| 1 | 8 | 11 |
| 1 | 13 | 17 |
| 1 | 18 | 20 |
+------+------+------+
3 rows in set (0.02 sec)

[懒得去死]

兄弟。你这是凌晨的回复阿。

[cerwg]

太好了，謝謝樓上的思路，我沒想到union還可以這樣用，腦子卡殼了，在那里join了很久，人品問題，汗：（

我把後面的插入、修改合并成了一條語句，用if()子句實現，再次感謝樓上。
（附）
insert into task
select setting.e_id,if(b1>bt,b1,bt) b1,if(e1<et,e1,et) e3 from setting ,task_backup t
where setting.e_id = t.e_id and bt<=e1 and et >=b1
union all
select setting.e_id,if(b2>bt,b2,bt) b2,if(e2<et,e2,et) e3 from setting ,task_backup t
where setting.e_id = t.e_id and bt<=e1 and et >=b1
union all
select setting.e_id,if(b3>bt,b3,bt) b2,if(e3<et,e3,et) e3 from setting ,task_backup t
where setting.e_id = t.e_id and bt<=e1 and et >=b1;

[cerwg]

更正
insert into task
select setting.e_id,if(b1>bt,b1,bt) b1,if(e1<et,e1,et) e1 from setting ,task_backup t
where setting.e_id = t.e_id and bt<=e1 and et >=b1
union all
select setting.e_id,if(b2>bt,b2,bt) b2,if(e2<et,e2,et) e2 from setting ,task_backup t
where setting.e_id = t.e_id and bt<=e2 and et >=b2
union all
select setting.e_id,if(b3>bt,b3,bt) b3,if(e3<et,e3,et) e3 from setting ,task_backup t
where setting.e_id = t.e_id and bt<=e3 and et >=b3;