如何将2个或多个日期范围合并为1个 [英] How to merge 2 or more date range into 1
问题描述
我正在使用MySQL 5.6
I am using MySQL 5.6
我正在处理一个复杂的业务问题.
I am working on a business problem which is complicated.
无论如何，我尝试使用此示例轻松地解释问题.
Anyway, I try to use this example to explain the problem easily.
有一个包含这些记录的表:
There is a table with these records:
-
Acct-1 2016-01-01 2016-01-07
Acct-1 2016-01-01 2016-01-07
Acct-1 2016-01-05 2016-01-11
Acct-1 2016-01-05 2016-01-11
Acct-1 2016-01-18 2016-01-24
Acct-1 2016-01-18 2016-01-24
Acct-1 2016-02-02 2016-02-08
Acct-1 2016-02-02 2016-02-08
Acct-1 2016-02-07 2016-02-13
Acct-1 2016-02-07 2016-02-13
第一列是帐号.在此示例中，所有帐号都是相同的.第二和第三列是日期范围.所有日期范围都恰好是7天.
The first column is the account number. In this example, all are the same account number. The 2nd and 3rd column is a date range. All date ranges are exactly 7 days.
我想让这张桌子更漂亮，以便将重叠范围合并"到一个日期范围内.
I want to make this table more beautiful to "merge" overlapping range together into a single date range.
因此，记录1和记录2应该合并在一起成为
So, record-1 and record-2 should be merge together to become:
Acct-1 2016-01-01 2016-01-11
Acct-1 2016-01-01 2016-01-11
Record-3是独立的，无需与其他人合并.
Record-3 is standalone, no need to merge with others.
Record-4和record-5应该合并在一起成为
Record-4 and record-5 should be merge together to become:
Acct-1 2016-02-02 2016-02-13
Acct-1 2016-02-02 2016-02-13
最后，每个帐户应具有不重叠日期范围的记录.
At the end, each account should have records of non-overlapping date range.
请注意，有时我们必须合并2或3或4或其他记录，具体取决于重叠情况.表格中有许多不同的帐号.
Noted that, sometimes we have to merge 2 or 3 or 4 or else records, depending on the overlapping. There are many different account numbers in the table.
如何使用SQL语句执行此操作?
How to do so using SQL statement ?
可以创建临时视图来达到目的.
Temp views can be created to achieve the purpose.
干杯
阿尔文
推荐答案
以下是使用用户变量的解决方案.已使用您提供的数据进行了验证，并且我插入了更多数据进行验证.
Here comes a solution using user variables. Verified with data you provided and I inserted more data for verification.
要永久保存查询结果并在以后使其可访问，请将结果写入表tbl_new中.
To save the query result permanently and keep it accessible later, the result is written into a table 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)
这篇关于如何将2个或多个日期范围合并为1个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
