MySQL 定位連續值範圍

備注:測試資料庫版本為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)