loading..
Русский    English
05:26
листать

Общие табличные выражения (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.  
  2. WITH Inc_Out(m_sum, type, date, point) AS (
  3.   SELECT inc, 'inc' type, date, point
  4.   FROM Income
  5.   UNION ALL
  6.   SELECT inc, 'inc' type, date, point
  7.   FROM Income_o
  8.   UNION ALL
  9.   SELECT out, 'out' type, date, point
  10.   FROM Outcome_o
  11.   UNION ALL
  12.   SELECT out, 'out' type,date, point FROM Outcome )
  13. SELECT 'max' min_max,* FROM Inc_Out
  14. WHERE m_sum >= ALL( SELECT m_sum FROM Inc_Out)
  15. UNION ALL
  16. SELECT 'min', * FROM Inc_Out
  17. WHERE m_sum <= ALL( SELECT m_sum FROM Inc_Out)
 

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

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

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

Содержание:

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