MySQL 定位连续值范围

叁歲伎倆 2022-12-19 01:20 124阅读 0赞

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