備注:測試資料庫版本為MySQL 8.0
測試資料:
create table v(proj_id int,proj_start date,proj_end date);
insert into v values (1,'2020-10-01','2020-10-02');
insert into v values (2,'2020-10-02','2020-10-03');
insert into v values (3,'2020-10-03','2020-10-04');
insert into v values (4,'2020-10-04','2020-10-05');
insert into v values (5,'2020-10-06','2020-10-07');
insert into v values (6,'2020-10-16','2020-10-17');
insert into v values (7,'2020-10-17','2020-10-18');
insert into v values (8,'2020-10-18','2020-10-19');
insert into v values (9,'2020-10-19','2020-10-20');
insert into v values (10,'2020-10-21','2020-10-22');
insert into v values (11,'2020-10-26','2020-10-27');
insert into v values (12,'2020-10-27','2020-10-28');
insert into v values (13,'2020-10-28','2020-10-29');
insert into v values (14,'2020-10-29','2020-10-30');
一.需求
确定哪些行表示連續工程的範圍。
下面給出了表V的結果集，其中包含有關工程及其開始日期和結束日期的資料:
mysql> select * from v;
±--------±-----------±-----------+
| proj_id | proj_start | proj_end |
±--------±-----------±-----------+
| 1 | 2020-10-01 | 2020-10-02 |
| 2 | 2020-10-02 | 2020-10-03 |
| 3 | 2020-10-03 | 2020-10-04 |
| 4 | 2020-10-04 | 2020-10-05 |
| 5 | 2020-10-06 | 2020-10-07 |
| 6 | 2020-10-16 | 2020-10-17 |
| 7 | 2020-10-17 | 2020-10-18 |
| 8 | 2020-10-18 | 2020-10-19 |
| 9 | 2020-10-19 | 2020-10-20 |
| 10 | 2020-10-21 | 2020-10-22 |
| 11 | 2020-10-26 | 2020-10-27 |
| 12 | 2020-10-27 | 2020-10-28 |
| 13 | 2020-10-28 | 2020-10-29 |
| 14 | 2020-10-29 | 2020-10-30 |
±--------±-----------±-----------+
14 rows in set (0.00 sec)
除第一行之外，每行的PROJ_START都應該等于它前一行的PROJ_END(“前一行”定義為: 目前航的PROJ_ID -1).
檢查表V的前5行，PROJ_ID值為1-3的工程都屬于同一“組”，其中每個PROJ_END都等于它下一行的PROJ_START值。
要找到連續工程的日期範圍，其中每個PROJ_END都等于它下一行的PROJ_START值。
要找到連續工程的日期範圍，應該傳回滿足下述條件的所有航，即目前行的PROJ_END值等于下一行的PROJ_START.
如果結果集中隻有前5行，則隻應傳回前3行。
最終結果集(使用表V共14行)應該是:
±--------±-----------±-----------+
| proj_id | proj_start | proj_end |
±--------±-----------±-----------+
| 1 | 2020-10-01 | 2020-10-02 |
| 2 | 2020-10-02 | 2020-10-03 |
| 3 | 2020-10-03 | 2020-10-04 |
| 6 | 2020-10-16 | 2020-10-17 |
| 7 | 2020-10-17 | 2020-10-18 |
| 8 | 2020-10-18 | 2020-10-19 |
| 11 | 2020-10-26 | 2020-10-27 |
| 12 | 2020-10-27 | 2020-10-28 |
| 13 | 2020-10-28 | 2020-10-29 |
±--------±-----------±-----------+
二.解決方案
解決方案其實就是使用表的自連接配接即可
select v1.proj_id,
v1.proj_start,
v1.proj_end
from v v1,v v2
where v1.proj_end = v2.proj_start;
測試記錄
mysql> select v1.proj_id,
-> v1.proj_start,
-> v1.proj_end
-> from v v1,v v2
-> where v1.proj_end = v2.proj_start;
+---------+------------+------------+
| proj_id | proj_start | proj_end |
+---------+------------+------------+
| 1 | 2020-10-01 | 2020-10-02 |
| 2 | 2020-10-02 | 2020-10-03 |
| 3 | 2020-10-03 | 2020-10-04 |
| 6 | 2020-10-16 | 2020-10-17 |
| 7 | 2020-10-17 | 2020-10-18 |
| 8 | 2020-10-18 | 2020-10-19 |
| 11 | 2020-10-26 | 2020-10-27 |
| 12 | 2020-10-27 | 2020-10-28 |
| 13 | 2020-10-28 | 2020-10-29 |
+---------+------------+------------+
9 rows in set (0.00 sec)