mysql - 如何将 2 个或多个日期范围合并为 1 个

Question

我正在使用 MySQL 5.6

我正在处理一个复杂的业务问题。

无论如何，我尽量用这个例子来说明问题。

有一个包含这些记录的表:

1. Acct-1 2016-01-01 2016-01-07

2. Acct-1 2016-01-05 2016-01-11

3. Acct-1 2016-01-18 2016-01-24

4. Acct-1 2016-02-02 2016-02-08

5. Acct-1 2016-02-07 2016-02-13

第一列是帐号。在这个例子中，所有都是相同的帐号。第二和第三列是日期范围。所有日期范围都恰好是 7 天。

我想让这张表更漂亮，以便将重叠范围“合并”到一个日期范围内。

因此，record-1 和 record-2 应该合并在一起成为:

Acct-1 2016-01-01 2016-01-11

Record-3 是独立的，不需要与其他的合并。

Record-4 和 record-5 应该合并在一起成为:

帐户 1 2016-02-02 2016-02-13

最后，每个账户应该有不重叠日期范围的记录。

请注意，有时我们必须合并 2 或 3 或 4 或其他记录，具体取决于重叠情况。表中有很多不同的帐号。

如何使用 SQL 语句做到这一点？

可以创建临时 View 来达到目的。

干杯，

阿尔文

Answer 1

这是一个使用用户变量的解决方案。已使用您提供的数据进行验证，我插入了更多数据以进行验证。

为了永久保存查询结果并使其以后可以访问，将结果写入表 tbl_new。

完整演示>>

SQL:

-- data preparation
create table tbl(acct varchar(100), start_date date, end_date date);
insert into tbl values
('Acct-1', '2016-01-01', '2016-01-07'),
('Acct-1', '2016-01-05', '2016-01-11'),
('Acct-1', '2016-01-18', '2016-01-24'),
('Acct-1', '2016-02-02', '2016-02-08'),
('Acct-1', '2016-02-07', '2016-02-13'),
('Acct-2', '2016-01-01', '2016-01-07'),
('Acct-2', '2016-01-18', '2016-01-24'),
('Acct-2', '2016-01-02', '2016-02-08');


SELECT * FROM tbl;

-- Need queries
SET @last_acct = '', @last_start_date = '1970-01-01', @last_end_date = '9999-12-31', @group = 1;

CREATE TABLE tbl_new (acct varchar(100), start_date date, end_date date);

INSERT INTO tbl_new 
SELECT 
    acct,
    MIN(start_date) start_date,
    MAX(end_date) end_date
FROM
(
    SELECT
        acct, 
        start_date, 
        end_date,
        CASE 
            WHEN 
                acct = @last_acct AND start_date <= @last_end_date 
            THEN 
                @group
            ELSE
                @group := @group + 1
        END group_num, 
        @last_acct := acct,
        @last_start_date := start_date,
        @last_end_date := end_date
    FROM
        tbl
) tbl2
GROUP BY group_num;

SELECT * FROM tbl_new;

输出:

mysql> SELECT * FROM tbl;
+--------+------------+------------+
| acct   | start_date | end_date   |
+--------+------------+------------+
| Acct-1 | 2016-01-01 | 2016-01-07 |
| Acct-1 | 2016-01-05 | 2016-01-11 |
| Acct-1 | 2016-01-18 | 2016-01-24 |
| Acct-1 | 2016-02-02 | 2016-02-08 |
| Acct-1 | 2016-02-07 | 2016-02-13 |
| Acct-2 | 2016-01-01 | 2016-01-07 |
| Acct-2 | 2016-01-18 | 2016-01-24 |
| Acct-2 | 2016-01-02 | 2016-02-08 |
+--------+------------+------------+
8 rows in set (0.00 sec)

mysql>
mysql> -- Need queries
mysql> SET @last_acct = '', @last_start_date = '1970-01-01', @last_end_date = '9999-12-31', @group = 1;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> CREATE TABLE tbl_new (acct varchar(100), start_date date, end_date date);
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO tbl_new
    -> SELECT
    ->     acct,
    ->     MIN(start_date) start_date,
    ->     MAX(end_date) end_date
    -> FROM
    -> (
    ->     SELECT
    ->         acct,
    ->         start_date,
    ->         end_date,
    ->         CASE
    ->             WHEN
    ->                 acct = @last_acct AND start_date <= @last_end_date
    ->             THEN
    ->                 @group
    ->             ELSE
    ->                 @group := @group + 1
    ->         END group_num,
    ->         @last_acct := acct,
    ->         @last_start_date := start_date,
    ->         @last_end_date := end_date
    ->     FROM
    ->         tbl
    -> ) tbl2
    -> GROUP BY group_num;

Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT * FROM tbl_new;
+--------+------------+------------+
| acct   | start_date | end_date   |
+--------+------------+------------+
| Acct-1 | 2016-01-01 | 2016-01-11 |
| Acct-1 | 2016-01-18 | 2016-01-24 |
| Acct-1 | 2016-02-02 | 2016-02-13 |
| Acct-2 | 2016-01-01 | 2016-01-07 |
| Acct-2 | 2016-01-02 | 2016-02-08 |
+--------+------------+------------+
5 rows in set (0.00 sec)