备注:测试数据库版本为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)