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

Question

某家工廠規定了員工上下班時間都必須打卡，包括上午、下午、晚上的工作段，每天正常工作情況下要打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】【】符號為本題提示信息。

Answer 1

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: 0mysql> 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: 0mysql> 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: 0mysql> update task set et = @et where et > @et;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from task;
+------+------+------+
| e_id | bt   | et   |
+------+------+------+
|    1 |    8 |   11 |
|    1 |   13 |   17 |
|    1 |   18 |   20 |
+------+------+------+
3 rows in set (0.02 sec)

Answer 2

太好了，謝謝樓上的思路，我沒想到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;

Answer 3

更正
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;