FirebirdSQL logo
Примечания
  • Определение CTE может содержать любой правильный оператор SELECT, если он не содержит преамбулы “WITH…​” (операторы WITH не могут быть вложенными);

  • CTE могут использовать друг друга, но ссылки не должны иметь циклов;

  • CTE могут быть использованы в любой части главного запроса или другого табличного выражения и сколько угодно раз;

  • Основной запрос может ссылаться на CTE несколько раз, но с разными алиасами;

  • CTE могут быть использованы в операторах INSERT, UPDATE и DELETE как подзапросы;

  • Если объявленное CTE не использовано, то будет выдано предупреждение “CTE cte is not used in query”. В более ранних версиях вместо предупреждения выдавалась ошибка;

  • CTE могут быть использованы и в PSQL в FOR циклах:

    FOR
      WITH
        MY_RIVERS AS (
          SELECT *
          FROM RIVERS
          WHERE OWNER = 'me'
        )
      SELECT
        NAME,
        LENGTH
      FROM MY_RIVERS
      INTO :RNAME,
           :RLEN
    DO
    BEGIN
      ...
    END
Примеры
Example 1. Запрос с использованием CTE
WITH
  DEPT_YEAR_BUDGET AS (
    SELECT
      FISCAL_YEAR,
      DEPT_NO,
      SUM(PROJECTED_BUDGET) BUDGET
    FROM PROJ_DEPT_BUDGET
    GROUP BY FISCAL_YEAR, DEPT_NO
  )
SELECT
  D.DEPT_NO,
  D.DEPARTMENT,
  DYB_2008.BUDGET BUDGET_08,
  DYB_2009.BUDGET AS BUDGET_09
FROM
  DEPARTMENT D
  LEFT JOIN DEPT_YEAR_BUDGET DYB_2008
    ON D.DEPT_NO = DYB_2008.DEPT_NO AND
       DYB_2008.FISCAL_YEAR = 2008
  LEFT JOIN DEPT_YEAR_BUDGET DYB_2009
    ON D.DEPT_NO = DYB_2009.DEPT_NO AND
       DYB_2009.FISCAL_YEAR = 2009
WHERE EXISTS (SELECT *
              FROM PROJ_DEPT_BUDGET B
              WHERE D.DEPT_NO = B.DEPT_NO)

Рекурсивные CTE

Рекурсивное (ссылающееся само на себя) CTE это UNION, у которого должен быть, по крайней мере, один не рекурсивный элемент, к которому привязываются остальные элементы объединения.Не рекурсивный элемент помещается в CTE первым.Рекурсивные члены отделяются от не рекурсивных и друг от друга с помощью UNION ALL.Объединение не рекурсивных элементов может быть любого типа.

Рекурсивное CTE требует наличия ключевого слова RECURSIVE справа от WITH.Каждый рекурсивный член объединения может сослаться на себя только один раз и это должно быть сделано в предложении FROM.

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

Выполнение рекурсивного CTE

Выполнение рекурсивного CTE с точки зрения сервера Firebird можно описать следующим образом:

  • Сервер начинает выполнение с не рекурсивного члена;

  • Для каждой выбранной строки из нерекурсивного части выполняется каждый рекурсивный член один за другим, используя текущие значения из предыдущей итерации как параметры;

  • Если во время выполнения экземпляр рекурсивного элемента не выдаёт строк, цикл выполнения переходит на предыдущий уровень и получает следующую строку от внешнего для него набора данных.

Примеры
Example 1. Рекурсивное CTE
WITH RECURSIVE
  DEPT_YEAR_BUDGET AS (
    SELECT
      FISCAL_YEAR,
      DEPT_NO,
      SUM(PROJECTED_BUDGET) BUDGET
    FROM PROJ_DEPT_BUDGET
    GROUP BY FISCAL_YEAR, DEPT_NO
  ),
  DEPT_TREE AS (
    SELECT
      DEPT_NO,
      HEAD_DEPT,
      DEPARTMENT,
      CAST('' AS VARCHAR(255)) AS INDENT
    FROM DEPARTMENT
    WHERE HEAD_DEPT IS NULL
    UNION ALL
    SELECT
      D.DEPT_NO,
      D.HEAD_DEPT,
      D.DEPARTMENT,
      H.INDENT || ' '
    FROM
      DEPARTMENT D
      JOIN DEPT_TREE H ON H.HEAD_DEPT = D.DEPT_NO
  )
SELECT
  D.DEPT_NO,
  D.INDENT || D.DEPARTMENT DEPARTMENT,
  DYB_2008.BUDGET AS BUDGET_08,
  DYB_2009.BUDGET AS BUDGET_09
FROM
  DEPT_TREE D
  LEFT JOIN DEPT_YEAR_BUDGET DYB_2008 ON
    (D.DEPT_NO = DYB_2008.DEPT_NO) AND
    (DYB_2008.FISCAL_YEAR = 2008)
  LEFT JOIN DEPT_YEAR_BUDGET DYB_2009 ON
    (D.DEPT_NO = DYB_2009.DEPT_NO) AND
    (DYB_2009.FISCAL_YEAR = 2009)

Следующий пример выводит родословную лошади.Основное отличие состоит в том, что рекурсия идёт сразу по двум веткам дерева родословной.

WITH RECURSIVE
  PEDIGREE (
    CODE_HORSE,
    CODE_FATHER,
    CODE_MOTHER,
    NAME,
    MARK,
    DEPTH
  ) AS (
    SELECT
      HORSE.CODE_HORSE,
      HORSE.CODE_FATHER,
      HORSE.CODE_MOTHER,
      HORSE.NAME,
      CAST('' AS VARCHAR(80)),
      0
    FROM HORSE
    WHERE
      HORSE.CODE_HORSE = :CODE_HORSE
    UNION ALL
    SELECT
      HORSE.CODE_HORSE,
      HORSE.CODE_FATHER,
      HORSE.CODE_MOTHER,
      HORSE.NAME,
      'F' || PEDIGREE.MARK,
      PEDIGREE.DEPTH + 1
    FROM
      HORSE
      JOIN PEDIGREE
        ON HORSE.CODE_HORSE = PEDIGREE.CODE_FATHER
    WHERE
      –- ограничение глубины рекурсии
      PEDIGREE.DEPTH < :MAX_DEPTH
    UNION ALL
    SELECT
      HORSE.CODE_HORSE,
      HORSE.CODE_FATHER,
      HORSE.CODE_MOTHER,
      HORSE.NAME,
      'M' || PEDIGREE.MARK,
      PEDIGREE.DEPTH + 1
    FROM
      HORSE
      JOIN PEDIGREE
        ON HORSE.CODE_HORSE = PEDIGREE.CODE_MOTHER
    WHERE
      –- ограничение глубины рекурсии
      PEDIGREE.DEPTH < :MAX_DEPTH
  )
SELECT
  CODE_HORSE,
  NAME,
  MARK,
  DEPTH
FROM
  PEDIGREE
Примечания для рекурсивного CTE:
  • В рекурсивных членах объединения не разрешается использовать агрегаты (DISTINCT, GROUP BY, HAVING) и агрегатные функции (SUM, COUNT, MAX и т.п.);

  • Рекурсивная ссылка не может быть участником внешнего объединения OUTER JOIN;

  • Максимальная глубина рекурсии составляет 1024;

  • Рекурсивный член не может быть представлен в виде производной таблицы.

Список полей SELECT

Список полей содержит одно или более выражений, разделённых запятыми.Результатом каждого выражения является значение соответствующего поля в наборе данных команды SELECT.Исключением является выражение * (“звёздочка”), которое возвращает все поля отношения.

Синтаксис
SELECT
  [...]
  [DISTINCT | ALL] <select-list>
  [...]
  FROM ...

<select-list> ::= * | <select-sublist> [, <select-sublist> ...]

<select-sublist> ::=
    <qualifier>.*
  | <value-expression> [COLLATE collation] [[AS] alias]



<value-expression> ::=
    [<qualifier>.]col_name
  | [<qualifier>.]selectable_SP_outparm
  | <literal>
  | <context-variable>
  | <function-call>
  | <single-value-subselect>
  | <CASE-construct>
  | <other-single-value-expr>
Table 1. Параметры списка полей оператора SELECT
Параметр Описание

qualifier

Имя таблицы (представления) или псевдоним таблицы (представления, хранимой процедуры, производной таблицы).

collation

Существующее имя сортировки (только для выражений символьных типов).

alias

Псевдоним поля.

col_name

Столбец таблицы или представления.

selectable-SP-outparm

Выходной параметр селективной хранимой процедуры.

literal

Литерал.

context-variable

Контекстная переменная.

function-call

Вызов скалярной, агрегатной или оконной функции.

single-value-subselect

Подзапрос, возвращающий единственное скалярное значение.

CASE-construct

Конструкция CASE.

other-single-value-expr

Любое другое выражение, возвращающее единственное значение типа данных Firebird или NULL.

Хорошим тоном является уточнять имя поля (или “*”) именем таблицы/представления/хранимой процедуры (или их псевдонимом), к которой это поле принадлежит. Например, relationname.columnname, relationname.*, alias.columnname, alias.*.Уточнение имени становится обязательным в случае, если поле с одним и тем же именем находится в более чем одном отношении, участвующей в объединении.Уточнение для “*” всегда обязательна, если это не единственный элемент в списке столбцов.

Important
Обратите внимание

Алиасы (псевдонимы) заменяют оригинальное имя таблицы, представления или хранимой процедуры: как только определён алиас для соответствующего отношения, использовать оригинальное имя нельзя.

В начало списка полей могут быть добавлены ключевые слова DISTINCT или ALL:

  • DISTINCT удаляет дубликаты строк: то есть, если две или более записей содержат одинаковые значения во всех соответствующих полях, только одна из этих строк будет включена в результирующий набор данных.

  • ALL включает все строки в результирующий набор данных. ALL включено по умолчанию и поэтому редко используется: явное указание поддерживается для совместимости со стандартом SQL.

Выражение COLLATE не изменяет содержимое поля, однако, если указать COLLATE для определённого поля, то это может изменить чувствительность к регистру символов или к акцентам (accent sensitivity), что, в свою очередь, может повлиять на:

  • Порядок сортировки, в случае если это поле указано в выражении ORDER BY;

  • Группировку, в случае если это поле указано в выражении GROUP BY;

  • Количество возвращаемых строк, если используется DISTINCT.

Примеры операторов SELECT с различными типами полей

Простой SELECT использующий только имена полей:

SELECT cust_id, cust_name, phone
FROM customers
WHERE city = 'London'

Запрос с конкатенацией и вызовом функции в списке полей:

SELECT
  'Mr./Mrs. ' || lastname,
  street,
  zip,
  upper(city)
FROM contacts
WHERE date_last_purchase(id) = current_date

Запрос с двумя подзапросами:

SELECT
  p.fullname,
  (SELECT name FROM classes c
   WHERE c.id = p.class) AS class,
  (SELECT name FROM mentors m
   WHERE m.id = p.mentor) AS mentor
FROM pupils p

Следующий запрос делает то же самое, что и предыдущий, только с использованием соединения таблиц (JOIN) вместо подзапросов:

SELECT
  p.fullname,
  c.name AS class,
  m.name AS mentor
FROM pupils p
  JOIN classes c ON c.id = p.class
  JOIN mentors m ON m.id = p.mentor

Этот запрос использует конструкцию CASE для определения корректного обращения, например, при рассылке сообщений конкретному человеку:

SELECT
  CASE upper(sex)
    WHEN 'F' THEN 'Mrs.'
    WHEN 'M' THEN 'Mr.'
    ELSE ''
  END AS title,
  lastname,
  address
FROM employees

Запрос с использованием оконной функции.Выводит сотрудников отранжированных по заработной плате.

SELECT
    id,
    salary,
    name,
    DENSE_RANK() OVER(ORDER BY salary) AS EMP_RANK
FROM employees
ORDER BY salary;

Запрос к хранимой процедуре:

SELECT *
FROM interesting_transactions(2010, 3, 'S')
ORDER BY amount

Выборка полей производной таблицы.Производная таблица – это заключённый в скобки оператор SELECT, результат которого используется в запросе уровнем выше, как будто является обычной таблицей или представлением.

SELECT
  fieldcount,
  COUNT(relation) AS num_tables
FROM
  (SELECT
     r.rdb$relation_name AS relation,
     COUNT(*) AS fieldcount
   FROM rdb$relations r
     JOIN rdb$relation_fields rf
       ON rf.rdb$relation_name = r.rdb$relation_name
   GROUP BY relation)
GROUP BY fieldcount

Запрос к контекстной переменной CURRENT_TIME:

SELECT current_time FROM rdb$database

Для тех, кто не знаком с RDB$DATABASE: это системная таблица, которая всегда существует во всех базах данных Firebird и всегда содержит только одну строку.И, хотя эта таблица не была создана специально для этой цели, стало распространённой практикой среди разработчиков Firebird выполнять запросы к этой таблице в случае, если нужно выполнить запрос, не привязанный ни к какой таблице, в котором результат получается из выражений, указанных в списке полей оператора SELECT.Например:

SELECT
  power(12, 2) AS twelve_squared,
  power(12, 3) AS twelve_cubed
FROM rdb$database

И, наконец, пример запроса к самой таблице RDB$DATABASE, с помощью которого можно получить кодировку по умолчанию данной БД:

SELECT rdb$character_set_name FROM rdb$database