loading..
Русский    English
01:21
листать

Общие табличные выражения (CTE) стр. 2

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

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

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

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

  1. WITH Trip_for_replace AS ( 
  2.   SELECT * FROM Pass_in_trip
  3.   WHERE trip_no=7772 AND date='20051129' )
  4. SELECT * FROM Trip_for_replace;

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

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

  1. WITH Trip_for_replace AS(
  2.   SELECT * FROM Pass_in_trip
  3.   WHERE trip_no=7772 AND date='20051129' ),
  4. Trip_7772 AS ( SELECT * FROM Trip WHERE trip_no=7772 )
  5. SELECT * FROM Trip_7772;

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

  1. WITH Trip_for_replace AS(
  2.   SELECT * FROM Pass_in_trip
  3.   WHERE trip_no=7772 AND date='20051129' ),
  4. Trip_7772 AS ( SELECT * FROM Trip WHERE trip_no=7772 ),
  5. Trip_candidates AS ( SELECT Trip.*
  6.       FROM Trip, Trip_7772
  7.       WHERE Trip.town_from+Trip.town_to = Trip_7772.town_from +
  8.      Trip_7772.town_to AND Trip.time_out > Trip_7772.time_out )
  9. SELECT * FROM Trip_candidates;

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

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

  1. WITH Trip_for_replace AS(
  2.   SELECT * FROM Pass_in_trip
  3.   WHERE trip_no=7772 AND date='20051129' ),
  4. Trip_7772 AS ( SELECT * FROM Trip WHERE trip_no=7772 ),
  5. Trip_candidates AS( SELECT Trip.* FROM Trip, Trip_7772
  6. WHERE Trip.town_from+Trip.town_to = Trip_7772.town_from +
  7.         Trip_7772.town_to AND Trip.time_out > Trip_7772.time_out ),
  8. Trip_replace AS(
  9. SELECT * FROM Trip_candidates
  10. WHERE time_out <= ALL(SELECT time_out FROM Trip_candidates) )
  11. SELECT * FROM Trip_replace;

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

  1. WITH Trip_for_replace AS(
  2.   SELECT * FROM Pass_in_trip
  3.   WHERE trip_no=7772 AND date='20051129' ),
  4.   Trip_7772 AS ( SELECT * FROM Trip WHERE trip_no=7772 ),
  5. Trip_candidates AS(
  6.   SELECT Trip.* FROM Trip, Trip_7772
  7.   WHERE Trip.town_from+Trip.town_to = Trip_7772.town_from +
  8.     Trip_7772.town_to AND Trip.time_out > Trip_7772.time_out ),
  9.     Trip_replace AS( SELECT * FROM Trip_candidates
  10.       WHERE time_out <= ALL(SELECT time_out FROM Trip_candidates) )
  11.  UPDATE Trip_for_replace SET trip_no = (SELECT trip_no FROM Trip_replace);

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

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

Bookmark and Share
Страницы: 1 2 3
Развернуть всё
Свернуть всё

Содержание:

Тэги:
ALL AND AUTO_INCREMENT AVG battles CASE CAST CHAR CHARINDEX CHECK classes COALESCE CONSTRAINT Convert COUNT CROSS APPLY CTE DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DELETE DISTINCT DML EXCEPT EXISTS EXTRACT FOREIGN KEY FROM FULL JOIN GROUP BY Guadalcanal HAVING IDENTITY IN INFORMATION_SCHEMA INNER JOIN insert INTERSECT IS NOT NULL IS NULL ISNULL laptop LEFT LEFT OUTER JOIN LEN maker Больше тэгов
Учебник обновлялся
месяц назад
https://exchangesumo.com/obmen/XVG-XEM/
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.