SQL - взаимосвязь между SubQuery и внешней таблицей

Проблема

Мне нужно лучше понять правила о том, когда я могу ссылаться на внешнюю таблицу в подзапросе и когда (и почему) это ненадлежащий запрос. Я обнаружил дублирование в запросе Oracle SQL, который я пытаюсь реорганизовать, но у меня возникают проблемы при попытке превратить мою ссылочную таблицу в сгруппированный subQuery.

Следующее утверждение работает надлежащим образом:

SELECT  t1.*  
FROM    table1 t1, 
INNER JOIN table2 t2 
        on t1.id = t2.id        
        and t2.date = (SELECT max(date) 
                       FROM   table2  
                       WHERE  id = t1.id) --This subquery has access to t1

К сожалению, table2 иногда имеет повторяющиеся записи, поэтому мне нужно сначала агрегировать t2, прежде чем присоединять его к t1. Однако, когда я пытаюсь обернуть его в подзапрос, чтобы выполнить эту операцию, внезапно механизм SQL больше не сможет распознать внешнюю таблицу.

SELECT  t1.* 
FROM    table1 t1, 
INNER JOIN (SELECT * 
            FROM  table2 t2
            WHERE t1.id = t2.id              --This loses access to t1
              and t2.date = (SELECT max(date) 
                             FROM   table2 
                             WHERE  id = t1.id)) sub on t1.id = sub.id 
                             --Subquery loses access to t1

Я знаю, что это принципиально разные запросы, которые я прошу, чтобы компилятор собрался вместе, но я не понимаю, почему он работает, но не другой.

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

Полезные ссылки

• Я нашел это фантастическое описание порядка, в котором предложения выполняются в SQL Server: (Предложение INNER JOIN ON vs WHERE). Я использую Oracle, но я думаю, что это будет стандартно по всем направлениям. Существует четкий порядок определения предложения (с первым FROM), поэтому я думаю, что любое предложение, появившееся дальше в списке, будет иметь доступ ко всей ранее обработанной информации. Я могу только предположить, что мой второй запрос каким-то образом изменяет порядок, чтобы мой подзапрос оценивался слишком рано?

• Кроме того, я нашел аналогичный вопрос (Ссылка на таблицы внешнего запроса в подзапросе ), но в то время как вход был хорош, они никогда не объясняли, почему он не может делать то, что он делает, и просто дал альтернативные решения своей проблемы. Я пробовал свои альтернативные решения, но это вызывало другие проблемы. А именно, этот подзапрос с ссылкой на дату является фундаментальным для всей операции, поэтому я не могу избавиться от нее.

Вопросы

• Я хочу понять, что я здесь сделал... Почему мой первоначальный подзапрос видит внешнюю таблицу, но не после того, как я завершаю весь оператор в подзапросе?

• Тем не менее, если то, что я пытаюсь сделать, не может быть сделано, каков наилучший способ рефакторинга первого запроса для устранения дублирования? Должен ли я дважды ссылаться на таблицу1 (со всем необходимым дублированием)? Или есть (возможно) лучший способ решить эту проблему?

Спасибо заранее!

------ ------ РЕДАКТИРОВАТЬ

Как некоторые из них предположили, что эти запросы выше, это не фактический запрос, который я рефакторинг, но пример проблемы, с которой я сталкиваюсь. Запрос, с которым я работаю, намного сложнее, поэтому я не решаюсь публиковать его здесь, так как я боюсь, что он заставит людей отключиться.

------ ------ ОБНОВЛЕНИЕ

Итак, я запускал это другим разработчиком, и у него было одно возможное объяснение того, почему мой подзапрос теряет доступ к t1. Поскольку я заключу этот подзапрос в круглую скобку, он считает, что этот подзапрос оценивается до того, как будет оценена моя таблица t1. Это определенно объясняет ошибку "ORA-00904:" t1 "." Id ": неверный идентификатор, который я получил. Это также предполагает, что, подобно арифметическому порядку операций, добавление parens в оператор дает ему приоритет в определенных оценках клаузулы. Я все равно буду любить эксперта, чтобы взвесить, если они согласятся/не согласны, что является логическим объяснением того, что я вижу здесь.

Ответы

SELECT t1.* FROM 
(
  SELECT * 
  FROM 
  (
    SELECT t2.id,
    RANK() OVER (PARTITION BY t2.id, t2.date ORDER BY t2.date DESC) AS R  
    FROM table2 t2
  )
  WHERE R = 1
) sub 
INNER JOIN table1 t1 
ON t1.id = sub.id