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

проблема

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

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

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 против предложения WHERE). Я использую Oracle, но я думаю, что это будет стандартным для всех. Существует четкий порядок оценки предложений (в первую очередь FROM), поэтому я думаю, что любое предложение, находящееся далее в списке, будет иметь доступ ко всей ранее обработанной информации. Я могу только предположить, что мой второй запрос каким-то образом изменит этот порядок, так что мой подзапрос оценивается слишком рано?

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

Вопросы

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

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

Заранее спасибо!

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

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

------ОБНОВИТЬ------

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

Источник

3 ответа

Решение

Так что я понял это на основе комментария, который Мартин Смит сделал выше (СПАСИБО МАРТИНУ!), И я хотел убедиться, что я поделился своим открытием для всех, кто сталкивается с этой проблемой.

Технические соображения

Во-первых, безусловно, было бы полезно, если бы я использовал правильную терминологию для описания своей проблемы: мое первое утверждение выше использует коррелированный подзапрос:

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

Мое второе утверждение с другой стороны было использование так называемого встроенного представления в Oracle, также известного как производная таблица в SQL Server:

Встроенное представление / производная таблица создает временное безымянное представление в начале вашего запроса, а затем обрабатывает его как другую таблицу до завершения операции. Поскольку компилятору необходимо создать временное представление, когда он видит один из этих подзапросов в строке FROM, эти подзапросы должны быть полностью автономными без ссылок вне подзапроса.

Почему то, что я делал, было глупо

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

Временное решение

Наконец, стоит отметить, что Мартин предложил довольно умный, но в конечном итоге неэффективный способ выполнить то, что я пытался сделать. Оператор Apply является проприетарной функцией SQL Server, но он позволяет вам общаться с объектами за пределами вашей производной таблицы:

Аналогично, эта функциональность доступна в Oracle через другой синтаксис:

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

Источник

Как насчет следующего запроса:

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
Источник

Во втором примере вы пытаетесь передать ссылку t1 вниз на 2 уровня. Вы не можете этого сделать, вы можете пропустить ее только на 1 уровень (именно поэтому первый работает). Если вы дадите лучший пример того, что вы пытаетесь сделать, мы также можем помочь вам переписать ваш запрос.

Источник
Другие вопросы по тегам