MySQL - Как сделать самостоятельное соединение, чтобы вернуть перекрывающиеся диапазоны дат?

Question

MySQL - Как сделать самостоятельное соединение, чтобы вернуть перекрывающиеся диапазоны дат?

У меня есть таблица с историческим положением о членстве в организации (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'

1

mysql self-join overlapping-matches

Источник

user3209752 07 окт '16 в 11:32

1 ответ

Другие вопросы по тегам mysql self-join overlapping-matches

user3209752 07 окт '16 в 20:02 2016-10-07 20:02 · Answer 1 · 2016-10-07 20:02

Потратив на это день и подумав, бегая на беговой дорожке, я думаю, что у меня есть ответ. Я публикую это здесь для пользы других. Я переместил соединение с tbl_member в подзапрос, и для полноты картины включил еще один подзапрос, чтобы получить фактический заголовок позиции из третьей таблицы, tbl_position, показанной ниже. (Не вижу способа заменить подзапросы на объединения, но это не имеет значения.)

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

Ниже приведен код, который работает правильно и показывает все совпадения с подробностями того, кто, с кем, когда и почему перекрывается.

Единственное препятствие в том, что если, например, показано, что Фред частично совпадает как президент с существующим послужным списком Джима по той причине, что срок полномочий Фреда полностью охватывает срок полномочий Джима, то также показано, что положение Джима как президента совпадает с существующим отчетом о пребывании в должности Фреда по причине учитывая, что Фред полностью закрыт Джимом. т.е. я получаю обе стороны перекрытия.

Если есть быстрый способ перекрытия "в одну сторону", тогда обязательно опубликуйте лучший ответ.

Мой ответ

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 ;