mysql - Mysql查询中的复杂重叠

Question

这是我的问题，我有一个包含以下列和数据示例的 MYSQL 表:

id | user | starting date | ending date | activity code
1 | Andy | 2010-04-01 | 2010-05-01 | 3
2 | Andy | 1988-11-01 | 1991-03-01 | 3
3 | Andy | 2005-06-01 | 2008-08-01 | 3
4 | Andy | 2005-08-01 | 2008-11-01 | 3
5 | Andy | 2005-06-01 | 2010-05-01 | 4
6 | Ben  | 2010-03-01 | 2011-06-01 | 3
7 | Ben  | 2010-03-01 | 2010-05-01 | 4
8 | Ben  | 2005-04-01 | 2011-05-01 | 3

如您在此表中所见，用户可以具有相同的事件代码和相似的日期或期间。并且对于同一用户，周期可以与其他用户重叠或不重叠。表格中也可以有多个重叠时段。

我想要的是一个 MYSQL QUERY 来获得以下结果:

new id | user | starting date | ending date | activity code
1 | Andy | 2010-04-01 | 2010-05-01 | 3 => ok, no overlap period
2 | Andy | 1988-11-01 | 1991-03-01 | 3 => ok, no overlap period
3 | Andy | 2005-06-01 | 2008-11-01 | 3 => same user, same activity but ending date coming from row 4 as extended period 
4 | Andy | 2005-06-01 | 2010-05-01 | 4 => ok other activity code
5 | Ben  | 2005-04-01 | 2011-06-01 | 3 => ok other user, but as overlap period rows 6 and 8 for the same user and activity, I take the widest range
6 | Ben  | 2010-03-01 | 2010-05-01 | 4 => ok other activity for second user

换句话说，对于相同的用户和事件代码，如果没有重叠，我需要的是开始日期和结束日期。如果同一用户和事件代码有重叠，我需要来自不同相关行的较低开始日期和较高结束日期。我需要这个表的所有用户和事件代码以及 MYSQL 的 SQL。

我希望它足够清楚并且有人可以帮助我，因为我尝试了本网站和其他网站提供的解决方案的不同代码但没有成功。

Answer 1

我有一些复杂的(严格来说是特定于 MySQL 的)解决方案:

SET @user = NULL;
SET @activity = NULL;
SET @interval_id = 0;

SELECT
  MIN(inn.`starting date`) AS start,
  MAX(inn.`ending date`) AS end,
  inn.user,
  inn.`activity code`
  FROM
    (SELECT
       IF(user <> @user OR `activity code` <> @activity,  
          @interval_id := @interval_id  + 1, NULL),
       IF(user <> @user OR `activity code` <> @activity,  
          @interval_end := STR_TO_DATE('',''), NULL),
       @user := user,
       @activity := `activity code`,
       @interval_id := IF(`starting date` > @interval_end,
                          @interval_id + 1,
                          @interval_id) AS interval_id,
       @interval_end := IF(`starting date` < @interval_end,
                           GREATEST(@interval_end, `ending date`),
                           `ending date`) AS interval_end,
       t.*
     FROM Table1 t
     ORDER BY t.user, t.`activity code`, t.`starting date`, t.`ending date`) inn
GROUP BY inn.user, inn.`activity code`, inn.interval_id;

基本思想是从第一个答案中无耻地借用的 this question .

您可以使用这个 SQL Fiddle查看结果并尝试不同的源数据。