Новое в стандарте и реализациях языка SQL

Заметим, что CTE могут использоваться не только с оператором SELECT, но и с другими операторами языка DML. Давайте решим такую задачу:

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

Т.е. эта задача на обновление записей в таблице Pass_in_trip. Я не буду приводить здесь решение этой задачи, которое не использует CTE, но вы можете сами это сделать, чтобы сравнить объемы кода двух решений.

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

WITH Trip_for_replace AS (  
  SELECT * FROM Pass_in_trip 
  WHERE trip_no=7772 AND date='20051129' ) 
SELECT * FROM Trip_for_replace;

Поскольку CTE играют роль представлений, то их можно в принципе использовать для обновления данных. Слова «в принципе» означают, что CTE является обновляемым, если выполняются определенные условия, аналогичные условиям обновления представлений. В частности, в определении должна использоваться только одна таблица без группировки и вычисляемых столбцов. Отметим, что необходимые условия в нашем случае выполнены.

Теперь нам нужна информация о рейсе 7772 для того, чтобы найти ближайший к нему подходящий рейс. Добавим еще одно CTE в определение:

WITH Trip_for_replace AS( 
  SELECT * FROM Pass_in_trip 
  WHERE trip_no=7772 AND date='20051129' ), 
Trip_7772 AS ( SELECT * FROM Trip WHERE trip_no=7772 ) 
SELECT * FROM Trip_7772;

Обратите внимание, что в одном запросе можно определить любое количество общих табличных выражений. И что особенно важно, CTE может включать ссылку на другое CTE, чем мы, собственно, сейчас и воспользуемся (обратите внимание на ссылку Trip_7772 в определении Trip_candidates).

WITH Trip_for_replace AS( 
  SELECT * FROM Pass_in_trip 
  WHERE trip_no=7772 AND date='20051129' ), 
Trip_7772 AS ( SELECT * FROM Trip WHERE trip_no=7772 ),
Trip_candidates AS ( SELECT Trip.* 
      FROM Trip, Trip_7772 
      WHERE Trip.town_from+Trip.town_to = Trip_7772.town_from +
     Trip_7772.town_to AND Trip.time_out > Trip_7772.time_out ) 
SELECT * FROM Trip_candidates;

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

Найдем теперь среди строк-кандидатов наиболее близкий по времени рейс:

WITH Trip_for_replace AS( 
  SELECT * FROM Pass_in_trip 
  WHERE trip_no=7772 AND date='20051129' ), 
Trip_7772 AS ( SELECT * FROM Trip WHERE trip_no=7772 ), 
Trip_candidates AS( SELECT Trip.* FROM Trip, Trip_7772 
WHERE Trip.town_from+Trip.town_to = Trip_7772.town_from + 
        Trip_7772.town_to AND Trip.time_out > Trip_7772.time_out ), 
Trip_replace AS( 
SELECT * FROM Trip_candidates 
WHERE time_out <= ALL(SELECT time_out FROM Trip_candidates) ) 
SELECT * FROM Trip_replace;

Теперь нам осталось последний оператор SELECT заменить на UPDATE, чтобы решить задачу:

WITH Trip_for_replace AS( 
  SELECT * FROM Pass_in_trip 
  WHERE trip_no=7772 AND date='20051129' ), 
  Trip_7772 AS ( SELECT * FROM Trip WHERE trip_no=7772 ), 
Trip_candidates AS( 
  SELECT Trip.* FROM Trip, Trip_7772 
  WHERE Trip.town_from+Trip.town_to = Trip_7772.town_from + 
    Trip_7772.town_to AND Trip.time_out > Trip_7772.time_out ), 
    Trip_replace AS( SELECT * FROM Trip_candidates 
      WHERE time_out <= ALL(SELECT time_out FROM Trip_candidates) )
 UPDATE Trip_for_replace SET trip_no = (SELECT trip_no FROM Trip_replace);

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

С использованием  CTE (общее табличное выражение) позволяет в рамках запроса задать таблицу, на которую можно многократно ссылаться.CTE с оператором DELETE вы можете познакомиться на примере удаления дубликатов строк из таблицы.

Содержание:

Рекурсивные СТЕ