4 Jean Blue
2 Kevin B la ck
3 K e ith Long
3 Frank Howard
3 Doreen Penn
Хотя Ron Johnson исключен из результатов, его служащие Fred Hobbs и
Rob Green остались включенными в эти результаты. Чтобы исключить из
результатов запроса целиком всю ветвь узлов, можно добавить во фразу
CONNECT BY PRIOR фразу AND. Следующий запрос использует фразу AND
last_name != ' Johnson’ для исключения из результатов запроса служащего
Rob Jones и всех его подчиненных:
□ SELECT LEVEL
LPADC 2 * LEVEL - 1) || first_name || ' ' ||
last_name AS employee
FROM more_employees
START WITH employee_id = 1
CONNECT BY PRIOR employee_id = manager_id
AND last_name != ‘Johnson’;
LEVEL EMPLOYEE
1 James Smith
2 Susan Jones
3 Jane Brown
4 Henry Heyson
3 John Grey
4 Jean Blue
2 Kevin Black
3 K e ith Long
3 Frank Howard
3 Doreen Penn
Включение в иерархический запрос других условий
Можно включить в иерархический запрос другие условия, используя
фразу WHERE. Следующий запрос использует фразу WHERE для того, чтобы
Расширенные запросы 209
включить в результаты запроса только тех служащих, зарплата которых не
превышает $50000:
□ SELECT LEVEL
LPADC *. 2 * LEVEL - 1) || first_name || ‘ ' ||
last_name AS employee, salary
FROM more_employees
WHERE salary <= 50000
START WITH employee.id = 1
CONNECT BY PRIOR employee_id = manager_id;
LEVEL EMPLOYEE SALARY
3 Rob Green 40000
3 Jane Brown 45000
4 Henry Heyson 30000
3 John Grey 30000
4 Jean Blue 29000
3 K e ith Long 50000
3 Frank Howard 45000
3 Doreen Penn - 47000
На этом мы закончим обсуждение иерархических запросов. В следующем
разделе вы ознакомитесь с расширенными фразами GROUP BY.
Использование расширенных фраз GROUP BY
В этом разделе рассматриваются
■ Фраза ROLLUP, которая расширяет фразу GROUP BY, чтобы она возвращала
строки, содержащие предварительные итоги для каждой группы,
а также строку, содержащую общий итог для всех групп.
■ Фраза CUBE, которая расширяет фразу GROUP BY, чтобы она возвращала
строки, содержащие предварительные итоги для каждой комбинации
столбцов, и общий итог в конце.
Таблицы, используемые в примерах
Вы увидите, как используются следующие таблицы, уточняющие образы
сотрудников нашего воображаемого магазина:
■ divisions Здесь хранится информация о подразделениях компании;
■ jobs Здесь хранится информация об имеющихся в компании должностях;
■ employees2 Здесь хранится список сотрудников.
Эти таблицы создаются сценарием store_schema.sql. Таблица divisions
создается с помощью следующего выражения:
П CREATE TABLE d iv is io n s (
d iv is io n _ id CHAR(3)
CONSTRAINT d iv is io n s _ p k PRIMARY KEY,
210 Гпава 7
name VARCHAR2(15) NOT NULL
);
В таблице divisions содержатся следующие строки:
□ SELECT *
FROM divisions;
DIV NAME
SAL S a le s
OPE Operations
SUP Support
BUS Business
Таблица jobs создается следующим оператором:
□ CREATE TABLE jo b s (
jo b _ id CHAR(3)
CONSTRAINT jobs_pk PRIMARY KEY.
name VARCHAR2(20) NOT NULL
): . •
В таблице jobs содержатся следующие строки:
□ SELECT *
FROM jobs;
JOB NAME
WOR Worker
MGR Manager
ENG Engineer
TEC T e ch n o lo g is t
PRE P re s id e n t
Таблица employees2 создается следующим оператором:
□ CREATE TABLE employees2 (
employee_id INTEGER
CONSTRAINT employees2_pk PRIMARY KEY,
d iv is io n _ id CHAR(3)
CONSTRAINT em p lo y e e 2 _ fk_ d iv isio n s
REFERENCES d iv is io n s ( d iv is io n _ id ) ,
jo b _ id CHAR(3) REFERENCES jo b s ( jo b _ id ) ,
firs t_ n am e VARCHAR2(10) NOT NULL,
last_name VARCHAR2(10) NOT NULL,
s a la r y NUMBER(6, 0)
);
Следующие выходные данные иллюстрируют первые пять строк таблицы
employees2 :
□ SELECT *
FROM employees2
WHERE ROWNUM <= 5;
Расширенные запросы 211
ДО DIV JOB FIRST_MAME LAST_NAME SALARY
1 BUS PRE James Smith 800000
2 SAL MGR Ron Johnson 350000
3 SAL WOR Fred Hobbs 1400004
SUP MGR Susan Jones 200000
5 SAL WOR Rob Green 350000
Использование фразы ROLLUP
Фраза ROLLUP расширяет возможности GROUP BY, возвращая строку, содержащую
предварительные итоги для каждой группы строк и общий итог
для всех групп.
Фраза GROUP BY используется для группировки строк в блоки с общим
значением столбца, по которому производится группировка (см. главу 4).
Следующий запрос использует фразу GROUP BY для группировки строк таблицы
employees2 по значениям столбца department_id и функцию SUM( ) для
получения суммы зарплат для d iv is io n _ id :
□ SELECT division_id, SUM(salary)
FROM employees2
GROUP BY division_id
ORDER BY division_id;
DIV SUM(SALARY)
BUS 1610000
OPE 1320000
SAL 4936000
SUP 1015000
Передача в R O L L U P одного столбца
Следующий запрос переписывает предыдущий пример с использованием
фразы ROLLUP. Обратите внимание на дополнительную строку в конце выходных
данных, содержащую общую заработную плату для всех групп:
□ SELECT division_id, SUM(salary)
FROM employees2
GROUP BY ROLLUP(division_id)
ORDER BY division_id;