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

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

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

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

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

Консоль
Выполнить
  1. SELECT inc AS max_sum, type, date, point 
  2. FROM ( SELECT inc, 'inc' type, date, point 
  3. FROM Income UNION ALL SELECT inc, 'inc' type, date, point 
  4. FROM Income_o 
  5. UNION ALL 
  6. SELECT out, 'out' type, date, point 
  7. FROM Outcome_o 
  8. UNION ALL 
  9. SELECT out, 'out' type, date, point FROM Outcome ) X 
  10. WHERE inc >= ALL( SELECT inc FROM Income 
  11.        UNION ALL 
  12.        SELECT inc FROM Income_o 
  13.        UNION ALL SELECT out FROM Outcome_o 
  14.        UNION ALL SELECT out FROM Outcome );

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

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

  1. CREATE VIEW Inc_Out AS 
  2. SELECT inc, 'inc' type, date, point 
  3. FROM Income 
  4. UNION ALL 
  5. SELECT inc, 'inc' type, date, point 
  6. FROM Income_o 
  7. UNION ALL 
  8. SELECT out, 'out' type, date, point 
  9. FROM Outcome_o 
  10. UNION ALL 
  11. SELECT out, 'out' type,date, point 
  12. FROM Outcome;
  13. GO
  14. SELECT inc AS max_sum, type, date, point 
  15. FROM Inc_Out WHERE inc >= ALL( SELECT inc FROM Inc_Out);

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

 

Консоль
Выполнить
  1. WITH Inc_Out AS (  
  2.   SELECT inc, 'inc' type, date, point 
  3.   FROM Income 
  4.   UNION ALL 
  5.   SELECT inc, 'inc' type, date, point 
  6.   FROM Income_o 
  7.   UNION ALL 
  8.   SELECT out, 'out' type, date, point 
  9.   FROM Outcome_o 
  10.   UNION ALL 
  11.   SELECT out, 'out' type,date, point FROM Outcome ) 
  12. SELECT inc AS max_sum, type, date, point 
  13. FROM Inc_Out WHERE inc >= ALL ( SELECT inc FROM Inc_Out);
 

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

 

Консоль
Выполнить
  1. WITH Inc_Out(m_sum, type, date, point) AS ( 
  2.   SELECT inc, 'inc' type, date, point 
  3.   FROM Income 
  4.   UNION ALL 
  5.   SELECT inc, 'inc' type, date, point 
  6.   FROM Income_o 
  7.   UNION ALL 
  8.   SELECT out, 'out' type, date, point 
  9.   FROM Outcome_o 
  10.   UNION ALL 
  11.   SELECT out, 'out' type,date, point FROM Outcome ) 
  12. SELECT 'max' min_max,* FROM Inc_Out 
  13. WHERE m_sum >= ALL( SELECT m_sum FROM Inc_Out) 
  14. UNION ALL 
  15. SELECT 'min', * FROM Inc_Out 
  16. WHERE m_sum <= ALL( SELECT m_sum FROM Inc_Out);
 

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

Рекомендуемые упражнения:  24, 116
Страницы: 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/BTC-NEO-sort
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.