Программирование >>  Построение запросов sql 

1 ... 40 41 42 [ 43 ] 44 45 46 ... 101


- рекурсивная часть может иметь только одну ссылку на саму себя и только в предложении FROM;

- рекурсивная ссылка не может участвовать во внешнем соединении таблиц. Запросы с рекурсивными производными таблицами выполняются

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

3.3.2.2.2. Простые подзапросы в предложении SELECT

Как уже отмечалось, в предложении SELECT могут использоваться простые и соотнесенные вложенные запросы.

При использовании простого подзапроса возвращенный им результат вставляется во все строки, формируемые внешним запросом. В предложении SELECT может использоваться только <скалярный подзапрос>, то есть подзапрос, который возвращает только одно значение.

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

SELECT AccountCD, AVG (PaySum) AS AVG Pay,

(SELECT AVG (NachislSum) FROM NachislSumma )

AS AVG All Nachisl

FROM PaySumma GROUP BY AccountCD;.

Результат выполнения запроса представлен на рис. 3.81.

ACCOUNTCD

AVG PAY

AVG ALL NACHISL

005488

55,70

45,17

015527

28,32

45,17

080047

42,81

45,17

080270

55,32

45,17

080613

22,93

45,17

115705

93,49

45,17

126112

20,30

45,17

136159

8,30

45,17

136160

28,15

45,17

136169

32,13

45,17

443069

48,77

45,17

443690

19,73

45,17

Рис. 3.81. Результат выполнения вложенного запроса



Как следует из этого примера, связь между значением, возвращаемым простым вложенным запросом (среднее значение всех начисленных сумм по всем абонентам), и значениями внешнего запроса фактически отсутствует. Поэтому простые подзапросы в предложении SELECT используются достаточно редко. Область применения связанных вложенных запросов в предложении SELECT намного шире и будет рассмотрена далее.

3.3.2.2.3. Простые подзапросы в предложении FROM

В соответствии со стандартом SQL2003 в предложении FROM могут быть определены не базовые таблицы, а производные таблицы, возвращаемые вложенным запросом (<табличный подзапрос>). Производные таблицы могут быть вложенными друг в друга и могут быть включены в соединение (неявное или явное) как обычные таблицы или представления.

Следует учесть, что:

- для определения производных таблиц можно использовать только простые подзапросы;

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

[AS] псевдоним (<список столбцов>);

- если используется конструкция [AS] псевдоним (<список столбцов>), то количество столбцов в скобках должно быть таким же, как и количество столбцов в предложении SELECT подзапроса.

Рассмотрим простейшее определение производной таблицы с помощью следующего подзапроса: SELECT *

FROM (SELECT AccountCD, Fio, Phone FROM Abonent)

AS A (ID, Full name, Tel);,

где A - псевдоним производной таблицы, а ID, Fullname, Tel - список ее столбцов.

Данный пример иллюстрирует правила использования подзапроса в предложении FROM, но использовать его нецелесообразно, так как выборку тех же значений можно получить, используя следующий обычный запрос: SELECT AccountCD, Fio, Phone FROM Abonent;.

Рассмотрим варианты, когда использование подзапроса в предложении FROM может оказаться более полезным.

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



SELECT (CAST (R.Req Count AS NUMERIC(5,2)) / A.Ab Count) AS Req on Ab

FROM (SELECT COUNT (*) FROM Abonent) AS A (Ab Count), (SELECT COUNT (*) FROM Request) AS R (Req Count);.

Результат выполнения запроса представлен на рис. 3.82.

REQ ON AB

1,75

Рис. 3.82. Результат выполнения вложенного запроса в предложении FROM

В настоящем примере используется функция CAST для преобразования вычисленного целого значения (Req Count) в десятичный формат NUMERIC(5,2). Если не сделать такое преобразование, то при делении целого числа на целое (R.Req Count/A.Ab Count) произойдет округление результата до целого в меньшую сторону (будет выведен результат Req on Ab=1).

Следует учесть, что предыдущий запрос выдаст ошибку деления на ноль, если таблица Abonent будет пустой. Чтобы исключить такую ошибку, можно записать предыдущий запрос в следующем виде: SELECT IIF(A.Ab Count > 0,

(CAST (R.Req Count AS NUMERIC(5,2))/ A.Ab Count), Нет ни одного абонента) AS Req on Ab FROM (SELECT COUNT (*) FROM Abonent)

AS A (Ab Count),

(SELECT COUNT (*) FROM Request) AS R (Req Count);.

В данном случае деление производится, только если количество абонентов больше нуля, в противном случае выводится Нет ни одного абонента.

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

SELECT (Count (*) абонентов подали Req Count

заявки) AS Info FROM (SELECT A.AccountCD, Count (*)

FROM Abonent A JOIN Request R

ON A.AccountCD = R.AccountCD GROUP BY AccountCD)

AS Ar (Abonent ID, Req Count) GROUP BY Ar.Req Count;.

Результат выполнения запроса представлен на рис. 3.83.



1 ... 40 41 42 [ 43 ] 44 45 46 ... 101

© 2006 - 2024 pmbk.ru. Генерация страницы: 0
При копировании материалов приветствуются ссылки.
Яндекс.Метрика