Выбрать максимальное значение во внутреннем запросе <другое во внешнем запросе

Question

У меня есть две таблицы аудита: Trip_aud и Event_aud. Они были созданы в Envers, но я опрашиваю их с помощью SQL. Они в основном такие же, как таблицы доменов, за исключением значения ревизии, которое увеличивается каждый раз, когда происходит изменение, и некоторый кортеж вставляется в таблицу аудита.

Когда Поездка меняется с определенного статуса (PLANNING -> EXECUTING), Я сохраняю его текущую ревизию, поэтому после этого я могу сравнить то, что было выполнено (скажем, время выхода) с тем, что было запланировано. Эти события (уход, остановка, ожидание...) сохраняются в событии с указателем на отключение. События тоже проверяются.

Envers работает как система CVS: если я запрашиваю какой-либо элемент с заданной ревизией, он ищет кортеж, максимальная ревизия которого меньше заданной ревизии. Ревизия, которая меня интересует, это та, которая находится в Trip, и хранится, когда она меняет состояние. Как я могу выбрать все события из поездки в данной ревизии?

Вот как выглядят таблицы. org_rev это ревизия поездки, где статус меняется.

Trip_aud
id  | rev | status   | org_rev | other columns...
----|-----|----------|---------|---------------
 1  |  1  |CREATED   |  NULL   |
 1  |  2  |OPTIMIZING|  NULL   |
 1  |  3  |PLANNED   |  NULL   |
 1  | ... |   ...    |  NULL   |
 1  |  44 |EXECUTING |   44    |
 1  |  58 |FINISHED  |   44    |

Event_aud
id  | trip_id | rev | start_time | other columns...
----|---------|-----|------------|---------------
 1  |    1    |  1  | 02:35:12   |
 2  |    1    |  1  | 03:14:84   |
 3  |    1    |  1  | 12:31:02   |
 1  |    1    |  2  | 04:00:00   |
 2  |    1    |  5  | 03:00:15   |
 2  |    1    |  10 | 05:49:59   |
 1  |    1    |  40 | 06:00:00   |
 1  |    1    |  58 | 06:07:39   |

Если я хочу поездку и события на пересмотре 3, я получаю

Trip_aud
id  | rev | status   | org_rev | other columns...
----|-----|----------|---------|---------------
 1  |  3  |PLANNED   |  NULL   | ...

 Event_aud
id  | trip_id | rev | start_time | other columns...
----|---------|-----|------------|---------------
 1  |    1    |  2  | 04:00:00   |
 2  |    1    |  1  | 03:14:84   |
 3  |    1    |  1  | 12:31:02   |

В пересмотре 44, когда планирование было закончено, оно

Trip_aud
id  | rev | status   | org_rev | other columns...
----|-----|----------|---------|---------------
 1  |  44 |EXECUTING |   44    |

Event_aud
id  | trip_id | rev | start_time | other columns...
----|---------|-----|------------|---------------
 1  |    1    |  40 | 06:00:00   |
 2  |    1    |  10 | 05:49:59   |
 3  |    1    |  1  | 12:31:02   |

Я сделал следующий запрос для сравнения планирования и выполнения, но он ничего не возвращает! Это делает самостоятельное соединение в EVENT_AUD, исключает дубликаты кортежей, которые различаются только по порядку их ревизий, и пытается выбрать максимальное rev меньше, чем org_rev в поездке.

 select t.id, planned.start_time, realized.start_time
 from 
     TRIP t
     inner join EVENT realized on realized.trip_id = t.id
     inner join EVENT_AUD planned on planned.id = realized.id
 where
     planned.id in
     (
         select ea1.id
         from
             EVENT_AUD ea1
             inner join EVENT_AUD ea2 on ea1.id = ea2.id
         where
            ea1.rev > ea2.rev
         group by ea1.id
         having max(ea1.rev) < t.org_rev
    )
    and t.id = {something given outside}

Любопытно, если я заменю t.org_rev с 44 ​​работает! Что я делаю неправильно?

Спасибо за любую помощь!

META: ожидается ли, что он предоставит небольшой пример базы данных в CSV, XML, INCLUDE INTO или что-то еще, чтобы люди могли протестировать SQL, который я запрашиваю? Как я могу прикрепить к вопросу?

Answer 1

Я думаю, что вам нужно присоединиться к ea1 и ea2 и для идентификатора поездки, потому что таким образом max дает максимальное число оборотов для всех событий. Это соединение также отсутствует между event и event_aud. Соответствующие потребности подзапроса

where ea1.trip_id = t.id

ОБНОВИТЬ:

Я не понял логику в запланированном в (выберите...), поэтому я изменил его на не существует:

 select t.id TripID, 
        planned.id pid, 
        planned.rev, 
        planned.start_time pst, 
        realized.start_time rst
 from 
     TRIP t
     inner join EVENT realized 
        on realized.trip_id = t.id
     inner join EVENT_AUD planned 
        on planned.id = realized.id
           and realized.trip_id = planned.trip_id
        -- Eliminate higher revisions
           and planned.rev < t.org_rev
 where not exists (select null 
             from event_aud ea
            where ea.trip_id = planned.trip_id
              and ea.id = planned.id
        -- Eliminate higher revisions
              and ea.rev < t.org_rev
        -- If there is higher revision than current not exists evaluates to false
              and ea.rev > planned.rev)
    and t.id = 1
order by 1, 2

Полный запрос в Sql Fiddle