MySQL находит дочерние записи, в которых дата создания находится до и после даты из родительской таблицы

Я использую MySQL версии 8.0.

У меня есть 2 таблицы: approvals и activities

Таблица approvals имеет связь один ко многим с таблицей activities, IE: одна запись approvals может иметь много записей в таблице activities.

В таблице activities будут записи, созданные как до, так и после создания записи в таблице approvals. Можно создать записи activities до создания записи approvals, потому что таблица activities фактически привязана к другой таблице, которая не важна для этого вопроса, а информация approvals обновляется в уже существующих записях таблицы activities после approvals запись создается.

Что я хочу сделать, так это написать запрос, который будет отображать информацию о записи approvals, а затем также отображать информацию о записях из таблицы activities для записей, которые были созданы прямо до и после создания записи approvals.

Я уже издевался над таблицами и моим запросом на db-fiddle здесь.

Вот сценарии для создания и заполнения таблиц approvals и activities:

CREATE TABLE `approvals` (`id` INTEGER NOT NULL AUTO_INCREMENT, `created` DATETIME, PRIMARY KEY(`id`));

CREATE TABLE `activities` (`id` INTEGER NOT NULL AUTO_INCREMENT, `approvalId` INTEGER, `created` DATETIME, PRIMARY KEY(`id`));

INSERT INTO `approvals` (`created`)
VALUES
('2020-10-04 10:30:05'),
('2020-10-04 10:42:21'),
('2020-10-05 11:24:47'),
('2020-10-06 15:35:35');

INSERT INTO `activities` (`approvalId`, `created`)
VALUES
(1, '2020-09-30 04:24:14'),
(1, '2020-10-01 12:21:05'),
(1, '2020-10-06 12:21:05'),
(1, '2020-10-07 14:29:22'),

(2, '2020-09-28 05:33:10'),
(2, '2020-10-04 09:21:05'),
(2, '2020-10-04 15:27:35'),
(2, '2020-10-07 01:44:12'),

(3, '2020-09-22 04:24:14'),
(3, '2020-10-04 12:21:05'),
(3, '2020-10-06 12:21:05'),
(3, '2020-10-07 14:29:22'),

(4, '2020-09-21 04:24:14'),
(4, '2020-10-04 14:21:05'),
(4, '2020-10-10 12:21:05'),
(4, '2020-10-12 14:29:22');

Вот запрос, который я написал:

SELECT `der`.`id` AS `aprvId`, 
    `oact1`.`id` AS `preActivityId`,
    `oact1`.`created` AS `prevActivityCreated`,        
    `der`.`created` AS `appCreatedDate`,    
    `oact2`.`id` AS `postActivityId`,
    `oact2`.`created` AS `postActivityCreated`    
FROM (
    SELECT `app`.`id`, 
        MAX(`act1`.`created`) AS `mxDate`,
        `app`.`created`,
        '' AS `mnDate`
    FROM `approvals` AS `app`
    JOIN `activities` AS `act1` ON `act1`.`approvalId` = `app`.`id`
    WHERE `act1`.`created` <= `app`.`created`
    GROUP BY `app`.`created`, `app`.`id`, `app`.`id`

    UNION ALL

    SELECT `app`.`id`,
        '' AS `mxDate`,
        `app`.`created`,
        MIN(`act1`.`created`) AS `mnDate`    
    FROM `approvals` AS `app`
    JOIN `activities` AS `act1` ON `act1`.`approvalId` = `app`.`id`    
    WHERE `act1`.`created` >= `app`.`created`
    GROUP BY `app`.`created`, `app`.`id`, `app`.`id`
) AS `der`
JOIN `activities` AS `oact1` ON `oact1`.`approvalId` = `der`.`id`
JOIN `activities` AS `oact2` ON `oact2`.`approvalId` = `der`.`id`
GROUP BY `der`.`id`, `oact1`.`id`, `oact2`.`id`, `der`.`created`
HAVING `prevActivityCreated` = MAX(`der`.`mxDate`) AND `postActivityCreated` = MAX(`der`.`mnDate`)

Вот результат моего запроса (опять же, этот запрос работает, и это результат, который мне нужен):


| aprvId | appCreatedDate      | preActivityId | prevActivityCreated | postActivityId | postActivityCreated |
| ------ | ------------------- | ------------- | ------------------- | -------------- | ------------------- |
| 1      | 2020-10-04 10:30:05 | 2             | 2020-10-01 12:21:05 | 3              | 2020-10-06 12:21:05 |
| 2      | 2020-10-04 10:42:21 | 6             | 2020-10-04 09:21:05 | 7              | 2020-10-04 15:27:35 |
| 3      | 2020-10-05 11:24:47 | 10            | 2020-10-04 12:21:05 | 11             | 2020-10-06 12:21:05 |
| 4      | 2020-10-06 15:35:35 | 14            | 2020-10-04 14:21:05 | 15             | 2020-10-10 12:21:05 |

Он предоставляет мне данные, которые я ищу, мой вопрос заключается в следующем: есть ли лучший, более чистый или более эффективный способ получения этой информации?


mysql sql query-performance
person Gharbad The Weak    schedule 22.10.2020    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Хм . . . Я бы подумал об этом как:

select aa.*, ac_prev.*, ac_next.*
from (select ap.id, ap.created,
             max(case when ac.created < ap.created then ac.created end) as prev_created,
             min(case when ac.created > ap.created then ac.created end) as next_created
      from approvals ap cross join
           activities ac 
      group by ap.id, ap.created
     ) aa left join
     activities ac_prev
     on ac_prev.created = aa.prev_created left join
     activities ac_next
     on ac_next.created = aa.next_created;

Вот скрипт db‹›.

person Gordon Linoff    schedule 22.10.2020
comment
этот запрос близок, но он возвращает 6 строк, хотя должно быть возвращено только 4. Набор результатов вашего запроса похож на мой набор результатов, но немного отличается. Спасибо хоть. - person Gharbad The Weak; 22.10.2020
comment
@GharbadTheWeak . . . Это ставит соответствующие действия в один ряд с утверждениями, что вполне соответствует духу вопроса. - person Gordon Linoff; 22.10.2020
comment
правда, но он по-прежнему создает несколько записей для некоторых approvalId, например для approvalId из 3. Это может показаться придирчивым, но мне нужно, чтобы набор результатов возвращал только 1 запись для каждого approvalId, точно так же, как набор результатов из моего запроса . - person Gharbad The Weak; 22.10.2020