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

Чтобы выяснить назначение общих табличных выражений, давайте начнем с примера.

Найти максимальную сумму прихода/расхода среди всех 4-х таблиц базы данных "Вторсырье", а также тип операции, дату и пункт приема, когда и где она была зафиксирована.

Задачу можно решить, например, следующим способом.

Консоль

Выполнить

SELECT inc AS max_sum, type, date, point 
FROM ( SELECT inc, 'inc' type, date, point 
FROM Income UNION ALL SELECT inc, 'inc' type, date, point 
FROM Income_o 
UNION ALL 
SELECT out, 'out' type, date, point 
FROM Outcome_o 
UNION ALL 
SELECT out, 'out' type, date, point FROM Outcome ) X 
WHERE inc >= ALL( SELECT inc FROM Income 
       UNION ALL 
       SELECT inc FROM Income_o 
       UNION ALL SELECT out FROM Outcome_o 
       UNION ALL SELECT out FROM Outcome )

SELECT inc as max_sum, type, date, point FROM ( SELECT inc, 'inc' type, date, point FROM Income UNION ALL SELECT inc, 'inc' type, date, point FROM Income_o UNION ALL SELECT out, 'out' type, date, point FROM Outcome_o UNION ALL SELECT out, 'out' type, date, point FROM Outcome ) X WHERE inc >= ALL( SELECT inc FROM Income        UNION ALL        SELECT inc FROM Income_o        UNION ALL SELECT out FROM Outcome_o        UNION ALL SELECT out FROM Outcome )

    Схема

Здесь мы сначала объединяем всю имеющуюся информацию, а затем выбираем только те строки, у которых сумма не меньше, чем каждая из сумм той же выборки из 4-х таблиц.

Фактически, мы дважды написали код объединений четырех таблиц. Как избежать этого? Можно создать представление, а затем адресовать запрос уже к нему:

CREATE VIEW Inc_Out AS 
SELECT inc, 'inc' type, date, point 
FROM Income 
UNION ALL 
SELECT inc, 'inc' type, date, point 
FROM Income_o 
UNION ALL 
SELECT out, 'out' type, date, point 
FROM Outcome_o 
UNION ALL 
SELECT out, 'out' type,date, point 
FROM Outcome 
GO 
SELECT inc AS max_sum, type, date, point 
FROM Inc_Out WHERE inc >= ALL( SELECT inc FROM Inc_Out)

Так вот, CTE играет роль представления, которое создается в рамках одного запроса и, не сохраняется как объект схемы. Предыдущий вариант решения можно переписать с помощью CTE следующим образом:

Консоль

Выполнить

WITH Inc_Out AS (  
  SELECT inc, 'inc' type, date, point 
  FROM Income 
  UNION ALL 
  SELECT inc, 'inc' type, date, point 
  FROM Income_o 
  UNION ALL 
  SELECT out, 'out' type, date, point 
  FROM Outcome_o 
  UNION ALL 
  SELECT out, 'out' type,date, point FROM Outcome ) 
SELECT inc AS max_sum, type, date, point 
FROM Inc_Out WHERE inc >= ALL ( SELECT inc FROM Inc_Out)

WITH Inc_Out AS (     SELECT inc, 'inc' type, date, point   FROM Income   UNION ALL   SELECT inc, 'inc' type, date, point   FROM Income_o   UNION ALL   SELECT out, 'out' type, date, point   FROM Outcome_o   UNION ALL   SELECT out, 'out' type,date, point FROM Outcome ) SELECT inc as max_sum, type, date, point FROM Inc_Out WHERE inc >= ALL ( SELECT inc FROM Inc_Out)

    Схема

Как видите, все аналогично использованию представления за исключением обязательных скобок, ограничивающих запрос; формально, достаточно лишь заменить CREATE VIEW на WITH. Как и для представления, в скобках после имени CTE может быть указан список столбцов, если нам потребуется включить их не все из подлежащего запроса и/или переименовать. Например, (я добавил дополнительно определение минимальной суммы в предыдущий запрос),

Консоль

Выполнить

 
WITH Inc_Out(m_sum, type, date, point) AS ( 
  SELECT inc, 'inc' type, date, point 
  FROM Income 
  UNION ALL 
  SELECT inc, 'inc' type, date, point 
  FROM Income_o 
  UNION ALL 
  SELECT out, 'out' type, date, point 
  FROM Outcome_o 
  UNION ALL 
  SELECT out, 'out' type,date, point FROM Outcome ) 
SELECT 'max' min_max,* FROM Inc_Out 
WHERE m_sum >= ALL( SELECT m_sum FROM Inc_Out) 
UNION ALL 
SELECT 'min', * FROM Inc_Out 
WHERE m_sum <= ALL( SELECT m_sum FROM Inc_Out)

  WITH Inc_Out(m_sum, type, date, point) AS (   SELECT inc, 'inc' type, date, point   FROM Income   UNION ALL   SELECT inc, 'inc' type, date, point   FROM Income_o   UNION ALL   SELECT out, 'out' type, date, point   FROM Outcome_o   UNION ALL   SELECT out, 'out' type,date, point FROM Outcome ) SELECT 'max' min_max,* FROM Inc_Out WHERE m_sum >= ALL( SELECT m_sum FROM Inc_Out) UNION ALL SELECT 'min', * FROM Inc_Out WHERE m_sum <= ALL( SELECT m_sum FROM Inc_Out)

    Схема

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

Рекомендуемые упражнения:  24

Содержание:

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