MySQL - 如何进行自连接以返回重叠的日期范围？

Question

我有一个组织内成员担任的历史职位任期表 (tbl_tenue)。每个职位一次只能由一个人担任，但一个人可以依次或同时担任多个职位。我想检查此表的完整性，方法是依次获取每个职位任期记录，然后将其与表中的所有其他记录进行比较，以查找(错误的)与同一职位的其他任期重叠。

我已经编写了一个工作查询(如下)，在传递 test_tenure_id、position_id 以及特定任期的开始和结束日期时返回重叠的详细信息(见下文)对于每个重叠。此查询返回 tenure_id、member_id、member_sn、date_started、date_ended 和重叠的原因。

任何人都可以帮助我使用 sql 现在针对同一个 tbl_tenue 表运行此查询，每次将 tbl_tenue 中一行的数据传递给它，这样我就可以测试每条记录是否与其他记录重叠(当然除了它本身)并从记录及其所有重叠中返回数据？

(我意识到，如果我能做到这一点，那么我应该能够避免使用连接将 tenure_id 传递给 WHERE 子句，也可以避免使用连接传递日期，但我不能看看目前如何做到这一点，所以任何帮助都会很好)

下面的查询使用下表，针对这个问题进行了简化

TABLE tbl_member 
  ( member_id INT AUTO_INCREMENT, -- pk
    member_sn` varchar(50) , --surname
    <other stuff>
  )

TABLE tbl_tenure
  (tenure_id INT AUTO_INCREMENT, -- pk
   member_id INT -- fk to tbl_member
   position_id -- fk to table of position titles
   date_started DATE
   date_ended DATE -- will be NULL if still in post
  )


  -- test data for query
  SET @the_test_tenure_start_date = '2016-05-13' ;
  SET @the_test_tenure_end_date = '2016-10-05';
  SET @the_test_position_id = 18; 
  SET @the_test_tenue_id = 122;

-- the query to return overlaps with data from a given tenure record  
SELECT
   tbl_tenure.tenure_id,
   tbl_tenure.member_id,
   tbl_member.member_sn,
   tbl_tenure.date_started,
   tbl_tenure.date_ended,
   CASE
      WHEN @the_test_tenure_end_date <= IFNULL(date_ended, CURDATE())  -- test end date <= existing end date
      AND @the_test_tenure_start_date >= date_started   -- test start date >= existing start date
      THEN 'Test dates fall completely inside an existing tenure'

      WHEN @the_test_tenure_end_date >= IFNULL(date_ended, CURDATE())   -- test end date >= existing end date
      AND @the_test_tenure_start_date <= date_started   -- test start date <= existing start date
      THEN 'An existing tenure falls completely inside test dates'

      WHEN @the_test_tenure_start_date >= date_started   -- test start date >= existing start date
      AND @the_test_tenure_start_date <= IFNULL(date_ended, CURDATE())   -- test start date <= existing end date
      THEN 'Test start date overlaps with an existing tenure'

      WHEN @the_test_tenure_end_date >= date_started   -- test end date >= existing start date
      AND @the_test_tenure_end_date <= IFNULL(date_ended, CURDATE())   -- test end date <= existing end date
      THEN 'Test end date overlaps with an existing tenure'
   END AS reason

FROM
   tbl_tenure
   INNER JOIN tbl_member
      ON tbl_tenure.member_id = tbl_member.member_id

WHERE ( -- there is an overlap (see qry 2.2 http://salman-w.blogspot.co.uk/2012/06/sql-query-overlapping-date-ranges.html
          @the_test_tenure_end_date >= date_started)    
          AND 
      IFNULL(date_ended, CURDATE()) >= @the_test_tenure_start_date
      )   
   AND tbl_tenure.position_id = @the_test_position_id   -- position to be tested
   AND  tbl_tenure.tenure_id <> @the_test_tenue_id    -- don't look at the test tenure record
ORDER BY tbl_tenure.date_started ASC;

为了澄清这个问题，我正在寻找的输出是这样的，注意 tenure_id 132 其中一个成员被记录为与自己重叠

tenure_id   | member_id     | position_id   | start_date | end_date   | overlapping_member_id | overlapping_tenure_id | overlapping_start_date |overlapping_end_date | overlap_reason
   123      |      2        |      6        | 2016-02-01 | 2016 02-01 |       7               |       456             |     2016-01-05         |   2016-01-10         |'Test start date overlaps with an existing tenure'
   125      |      2        |      8        | 2016-02-01 | 2016 03-01 |       8               |       459             |     2016-01-0          |   2016-02-01         |'Test end date overlaps with an existing tenure'
   129      |      4        |      7        | 2016-03-10 | 2016 04-01 |       6               |       501             |     2016-03-2          |   2016-03-25         |'An existing tenure falls completely inside test dates'
   132      |      4        |      7        | 2016-01-01 | 2016 04-01 |       4               |       505             |     2016-03-01         |   2016-04-01         |'Test end date overlaps with an existing tenure'
   135      |      9        |      3        | 2016-05-01 | 2016 07-01 |       9               |       520             |     2016-04-0          |   2016-08-01         |'Test dates fall completely inside an existing tenure'

Answer 1

在我花了一天时间在运行机上运行并思考之后，我相信我找到了答案。为了其他人的利益，我将其张贴在这里。我将与 tbl_member 的连接移动到子查询中，为了完整起见，包括另一个子查询以从第三个表 tbl_position 获取职位的实际标题，如下所示。 (看不到用连接替换子查询的方法，但这没关系。)

TABLE tbl_positions
   (
   position_id INT AUTO_INCREMENT, -- pk
   position VARCHAR(100), -- title of position
   <other stuff>
   )

下面是我想出的代码，它似乎可以正常工作，并显示了所有重叠以及谁与谁重叠的详细信息、时间和原因。

唯一的小问题是，例如，如果 Fred 的总统任期与 Jim 的现有记录重叠，原因是 Fred 的任期完全包含 Jim 的任期，那么 Jim 的总统职位也将与 Fred 的现有任期记录重叠原因是 Fred's 完全被 Jim's 包围了。即我得到了重叠的两边。

如果有一种快速获得“单向”重叠的方法，那么一定要发布一个更好的答案。

我的回答

SELECT
   base_tenure.position_id     AS base_tenure_id,
   base_tenure.member_id     AS base_member_id,
   (SELECT member_sn FROM tbl_member WHERE tbl_member.member_id = base_tenure.member_id)     AS base_sn,
   (SELECT tbl_positions.position FROM tbl_positions WHERE tbl_positions.position_id = base_tenure.position_id ) AS POSITION,
   base_tenure.date_started  AS base_date_started,
   base_tenure.date_ended    AS base_date_ended,

   overlap_tenure.position_id  AS overlap_tenure_id,
   overlap_tenure.member_id AS overlap_member_id,
   (SELECT member_sn FROM tbl_member WHERE tbl_member.member_id = overlap_tenure.member_id)     AS overlap_sn,
   overlap_tenure.date_started AS overlap_date_started,
   overlap_tenure.date_ended   AS overlap_date_ended,  

   CASE
      WHEN base_tenure.date_ended <= IFNULL(overlap_tenure.date_ended, CURDATE())-- test end date <= existing end date
      AND base_tenure.date_started >= overlap_tenure.date_started  -- test start date >= existing start date
      THEN 'tbl_member dates fall completely inside an existing tenue'

      WHEN base_tenure.date_ended >= IFNULL(overlap_tenure.date_ended, CURDATE()) -- test end date >= existing end date
      AND base_tenure.date_started <=  overlap_tenure.date_started -- test start date <= existing start date
      THEN 'An existing tenue falls completely inside tbl_member dates'

      WHEN base_tenure.date_started >= overlap_tenure.date_started  -- test start date >= existing start date
      AND base_tenure.date_started <= IFNULL( overlap_tenure.date_ended , CURDATE()) -- test start date <= existing end date
      THEN 'tbl_member start date overlaps with an existing tenue'

      WHEN base_tenure.date_ended >= overlap_tenure.date_started  -- test end date >= existing start date
      AND base_tenure.date_ended <= IFNULL( overlap_tenure.date_ended , CURDATE())-- test end date <= existing end date
      THEN 'tbl_member end date overlaps with an existing tenue'
   END AS reason

FROM -- a self join on tbl_tenure
   tbl_tenure AS base_tenure,
   tbl_tenure AS overlap_tenure

WHERE (-- there is an overlap (see qry 2.2 http://salman-w.blogspot.co.uk/2012/06/sql-query-overlapping-date-ranges.html
           base_tenure.date_ended  >= overlap_tenure.date_started -- test end date >= existing start date
          AND 
          IFNULL(overlap_tenure.date_ended, CURDATE()) >= base_tenure.date_started 
          )

   AND 
      base_tenure.club_function_id = overlap_tenure.club_function_id -- positions are the same for both members

   AND  
      base_tenure.position_id <> overlap_tenure.position_id --  don't compare the base record with itself as they are identical and will always overlap 

 ORDER BY
       (SELECT member_sn FROM tbl_member WHERE tbl_member.member_id = base_tenure.member_id) ,
       base_tenure.date_started ;