SQL - запросы
Выборка информации из одной таблицы:
describe tab;
select * from tab;
select col1, col2, col3 from tab;
select ROWID, col1 from tab;
select * from tab where tab_col_id = 15;
select 12 * 15 from dual;
select to_date('28-APR-1971') + 12 from dual;
select to_date('28-APR-1971') - 15 from dual;
select to_date('28-APR-1971') - to_date('28-JAN-1971') from dual;
select col1, col2, col3 + 45 from tab;
select 12 * (25/5 - 1) from dual;
select col1, col1 * 2 DOUBLE_COL1 from tab;
select col1, col1 * 2 "Double Col1" from tab;
select 12 * (25/5 - 1) AS "Result" from dual;
select col1 || ' ' || col2 AS "Concatenation" from tab;
select col1, col2 from tab where tab_col2 is null;
select col1, col2 from tab where tab_col2 is not null;
select col1, nvl(col2, 'Uncnown col2') AS COL_2 from tab;
select DISTINCT col1 from tab;
Сравнение значений:
select col1, col2, col3 from tab where col1 <> 12;
select col1, col2, col3 from tab where col2 > 10;
select col1, col2, col3 from tab where col3 <= 100;
select col1, col2, col3 from tab where ROWNUM <= 15;
select col1, col2, col3 from tab where col1 > ANY( 12, 18, 25 );
(истина, когда col1 больше одного любого из перечисленных чисел, фактически если >12)
select col1, col2, col3 from tab where col1 > SOME( 12, 18, 25 );
(истина, когда col1 больше одного любого из перечисленных чисел, фактически если >12)
select col1, col2, col3 from tab where col1 > ALL( 12, 18, 25 );
(истина, когда col1 одновременно больше всех из перечисленных чисел, это эквивалентно >25)
select col1, col2 from tab where col1 LIKE '_z%';
(истина, если вторая буква в строке z)
select col1, col2 from tab where col1 NOT LIKE '_z%';
(истина, если вторая буква в строке не z)
select col1, col2 from tab where col1 LIKE '%\%%' ESCAPE '\';
(истина, если в строке содержится символ %)
select col1, col2 from tab where col1 IN ( 12, 18, 25 );
select col1, col2 from tab where col1 NOT IN ( 12, 18, 25, null );
(если в списке NOT IN встретится null, то выражение вернет false)
select col1, col2 from tab where col1 BETWEEN ( 10 and 20 );
select col1, col2 from tab where col1 NOT BETWEEN ( 10 and 20 );
select col1, col2, col3 from tab where col1 > '28-JAN-1971' and col2 > 10;
select col1, col2, col3 from tab where col1 > '28-JAN-1971' or col2 > 10;
select col1, col2, col3 from tab where col1 > '28-JAN-1971' or ( col2 > 10 and col3 LIKE '_z%');
(у AND больший приоритет чем у OR)
select col1, col2, col3 from tab order by col1;
select col1, col2, col3 from tab order by 1;
select col1, col2, col3 from tab order by col1 ASC, col2 DESC;
Выборка строк из двух таблиц:
select tab1.col, tab2.col
from tab1, tab2
where tab1.col_tab2_id = tab2.col_tab2_id
and tab1.col_tab1_id = 100;
(используя псевдонимы)
select t1.col, t2.col
from tab1 t1, tab2 t2
where t1.col_tab2_id = t2.col_tab2_id
and t1.col_tab1_id = 100;
(Декартово произведение)
select t1.col, t2.col
from tab1 t1, tab2 t2
Выборка строк из более чем двух таблиц:
select t1.col, t2.col AS COL_T2, t3.col AS COL_T3
from tab1 t1, tab2 t2, tab3 t3, tab4 t4
where t1.col_tab1_id = t2.col_tab1_id
and t3.col_tab3_id = t2.col_tab3_id
and t3.col_tab4_id = t4.col_tab4_id
order by t3.col;
Типы соединений
В предыдущих примерах в условиях соединения использовался знак равенства (=),
поэтому такие соединения называют соединениями по эквивалентности equijoins.
Соединения по эквивалентности:
используется знак =
Соединения по неэквивалентности:
используются операторы <, >, between и т.д.
Кроме того есть
Внутренние соединения:
в столбцах условия соединения содержатся значения удовлетворяющие условию,
т.е. нет пустых значений.
Внешние соединения:
когда один из столбцов соединения может сожержать значения NULL.
Самосоединения:
возвращают соединенные строки одной и той же таблицы
Примеры:
(соединение по неэквивалентности)
select t1.col1, t2.col1, t2.col2, t2.col3
from tab1 t1, tab2 t2
where t1.col1 BETWEEN t2.col1 and t2.col2
order by t2.col3;
(внешнее соединение)
select t1.col, t2.col
from tab1 t1, tab2 t2
where t1.col_tab2_id = t2.col_tab2_id(+)
order by t1.col;
где (+), там могут быть пустые значения
(в нашем случае в таблице tab2)
Левое и правое внешние соединения
Левое соединение:
оператор внешнего соединения (+) появляется справа от знака =
where t1.col_tab2_id = t2.col_tab2_id(+)
Правое соединение:
оператор внешнего соединения (+) стоит слева от знака =
where t1.col_tab2_id(+) = t2.col_tab2_id
Ограничения внешних соединений
оператор (+) можно поместить только с одной стороны от знака =
нельзя использовать (+) с IN
where t1.col_tab2_id(+) IN ( 1, 2, 3 )
нельзя использовать (+) с OR
where .... (+) = .... OR .... = 10
(Левое внешнее соединение)
select t1.col, t2.col
from tab1 t1, tab2 t2
where t1.col_tab2_id = t2.col_tab2_id(+)
order by t1.col;
(Правое внешнее соединение)
select t1.col, t2.col
from tab1 t1, tab2 t2
where t1.col_tab2_id(+) = t2.col_tab2_id
order by t1.col;
(Полное внешнее соединение)
Двунаправленное внешнее соединение не разрешено:
where t1.col_tab2_id(+) = t2.col_tab2_id(+)
получим ошибку ORA-01468
Остается один выход, объединить два запроса:
select t1.col, t2.col
from tab1 t1, tab2 t2
where t1.col_tab2_id(+) = t2.col_tab2_id
UNION
select t1.col, t2.col
from tab1 t1, tab2 t2
where t1.col_tab2_id = t2.col_tab2_id(+);
Самосоединения selfjoin
получается в результате соединения таблицы с самой собой.
select t1.col, t2.col
from tab t1, tab t2
where t1.col1_id = t2.col2_id;
order by t1.col;
Можно выполнить внешнее соединение в сочетании с самосоединением:
select t1.col, t2.col
from tab t1, tab t2
where t1.col1_id(+) = t2.col2_id;
order by t1.col;
Новый синтаксис соединений SQL/92
(появился в версии 9i)
До этого мы пользовались стандартом SQL/86
Примеры:
SQL/86:
select t1.col, t2.col
from tab1 t1, tab2 t2
where t1.col_tab2_id = t2.col_tab2_id
order by t1.col;
SQL/92:
select t1.col, t2.col
from tab1 t1
INNER JOIN tab2 t2
ON t1.col_tab2_id = t2.col_tab2_id
order by t1.col;
Во фразе ON можно использовать операторы неэквивалентности:
ON t1.col1 BETWEEN t2.col1 and t2.col2
Если в запросе используется соединение по эквивалентности
и столбцы, по которым выполняется соединение, имеют одинаковые имена
то можно воспользоваться сокращенным синтаксисом:
select t1.col, t2.col
from tab1 t1
INNER JOIN tab2 t2
ON t1.col_tab2_id = t2.col_tab2_id
order by t1.col;
этот запрос можно переписать так:
select t1.col, t2.col
from tab1 t1
INNER JOIN tab2 t2
USING( col_tab2_id )
order by t1.col;
ON t1.col_tab2_id = t2.col_tab2_id
мы заменили на
USING( col_tab2_id )
Имя столбца соединения во фразе USING должно использоваться без псевдонимов
и теперь везде этот столбец нужно использовать без псевдонимов
в том числе и во фразе SELECT:
select t1.col, t2.col, col_tab2_id
from tab1 t1
INNER JOIN tab2 t2
USING( col_tab2_id )
order by t1.col;
Внутренние соединения более двух таблиц:
select t1.col, t2.col AS COL_T2, t3.col AS COL_T3
from tab1 t1, tab2 t2, tab3 t3, tab4 t4
where t1.col_tab1_id = t2.col_tab1_id
and t3.col_tab3_id = t2.col_tab3_id
and t3.col_tab4_id = t4.col_tab4_id
order by t3.col;
В нотации SQL/92 этот запрос можно переписать так:
select t1.col, t2.col AS COL_T2, t3.col AS COL_T3
from tab1 t1
INNER JOIN tab2 t2
ON t1.col_tab1_id = t2.col_tab1_id
INNER JOIN tab3 t3
ON t3.col_tab3_id = t2.col_tab3_id
INNER JOIN tab3 t4
ON t3.col_tab4_id = t4.col_tab4_id
order by t3.col;
или даже так:
select t1.col, t2.col AS COL_T2, t3.col AS COL_T3
from tab1 t1
INNER JOIN tab2 t2
USING(col_tab1_id)
INNER JOIN tab3 t3
USING(col_tab3_id)
INNER JOIN tab3 t4
USING(col_tab4_id)
order by t3.col;
Если в соединении используется более одного столбца из двух таблиц,
можно во фразе ON использовать оператор AND:
select t1.col, t2.col
from tab1 t1
INNER JOIN tab2 t2
ON t1.col1 = t2.col1
AND t1.col2 = t2.col2;
а если имена столбцов одинаковы и используются эквисоединение,
то запись можно упростить:
select t1.col, t2.col
from tab1 t1
INNER JOIN tab2 t2
USING(col1, col2);
Внешние соединения в нотации SQL/92
Существует три вида внешних соединений
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
Левое внешнее соединение:
select t1.col, t2.col
from tab1 t1, tab2 t2
where t1.col_tab2_id = t2.col_tab2_id(+)
order by t1.col;
в нотации SQL/92 будет выглядеть так:
select t1.col, t2.col
from tab1 t1
LEFT OUTER JOIN tab2 t2
ON t1.col_tab2_id = t2.col_tab2_id
order by t1.col;
или
select t1.col, t2.col
from tab1 t1
LEFT OUTER JOIN tab2 t2
USING(col_tab2_id)
order by t1.col;
Правое внешнее соединение
select t1.col, t2.col
from tab1 t1, tab2 t2
where t1.col_tab2_id(+) = t2.col_tab2_id
order by t1.col;
в нотации SQL/92 будет выглядеть так:
select t1.col, t2.col
from tab1 t1
RIGHT OUTER JOIN tab2 t2
ON t1.col_tab2_id = t2.col_tab2_id
order by t1.col;
или
select t1.col, t2.col
from tab1 t1
RIGHT OUTER JOIN tab2 t2
USING(col_tab2_id)
order by t1.col;
Полное внешнее соединение
select t1.col, t2.col
from tab1 t1, tab2 t2
where t1.col_tab2_id(+) = t2.col_tab2_id
UNION
select t1.col, t2.col
from tab1 t1, tab2 t2
where t1.col_tab2_id = t2.col_tab2_id(+);
в нотации SQL/92 будет выглядеть так:
select t1.col, t2.col
from tab1 t1
FULL OUTER JOIN tab2 t2
ON t1.col_tab2_id = t2.col_tab2_id
order by t1.col;
или
select t1.col, t2.col
from tab1 t1
FULL OUTER JOIN tab2 t2
USING(col_tab2_id)
order by t1.col;
Самосоединения selfjoin
когда таблица соединяется сама собой.
select t1.col, t2.col
from tab t1, tab t2
where t1.col1_id = t2.col2_id;
order by t1.col;
в нотации SQL/92 будет выглядеть так:
select t1.col, t2.col
from tab t1
INNER JOIN tab t2
ON t1.col1_id = t2.col2_id;
Декартово произведение в нотации SQL/92 создается так:
select t1.col, t2.col
from tab1 t1
CROSS JOIN tab2 t2;
Еще примеры:
drop table t;
create table t(id, type, text)
as
select object_id, object_type, object_name
from all_objects
order by 1;
drop table t1;
create table t1(id, type, text)
as
select id, type, text
from t
where rownum <= 5;
drop table t2;
create table t2
as
select id, type , text
from t
where rownum <= 10;
select id, type, text from t1;
2 CLUSTER C_OBJ#
3 INDEX I_OBJ#
4 TABLE TAB$
5 TABLE CLU$
6 CLUSTER C_TS#
select id, type, text from t2;
2 CLUSTER C_OBJ#
3 INDEX I_OBJ#
4 TABLE TAB$
5 TABLE CLU$
6 CLUSTER C_TS#
7 INDEX I_TS#
8 CLUSTER C_FILE#_BLOCK#
9 INDEX I_FILE#_BLOCK#
10 CLUSTER C_USER#
11 INDEX I_USER#
Работа с множествами:
select id, type, text from t1
UNION ALL
select id, type, text from t2;
2 CLUSTER C_OBJ#
3 INDEX I_OBJ#
4 TABLE TAB$
5 TABLE CLU$
6 CLUSTER C_TS#
2 CLUSTER C_OBJ#
3 INDEX I_OBJ#
4 TABLE TAB$
5 TABLE CLU$
6 CLUSTER C_TS#
7 INDEX I_TS#
8 CLUSTER C_FILE#_BLOCK#
9 INDEX I_FILE#_BLOCK#
10 CLUSTER C_USER#
11 INDEX I_USER#
select id, type, text from t1
UNION ALL
select id, type, text from t2
order by 1;
2 CLUSTER C_OBJ#
2 CLUSTER C_OBJ#
3 INDEX I_OBJ#
3 INDEX I_OBJ#
4 TABLE TAB$
4 TABLE TAB$
5 TABLE CLU$
5 TABLE CLU$
6 CLUSTER C_TS#
6 CLUSTER C_TS#
7 INDEX I_TS#
8 CLUSTER C_FILE#_BLOCK#
9 INDEX I_FILE#_BLOCK#
10 CLUSTER C_USER#
11 INDEX I_USER#
select id, type, text from t1
UNION
select id, type, text from t2;
2 CLUSTER C_OBJ#
3 INDEX I_OBJ#
4 TABLE TAB$
5 TABLE CLU$
6 CLUSTER C_TS#
7 INDEX I_TS#
8 CLUSTER C_FILE#_BLOCK#
9 INDEX I_FILE#_BLOCK#
10 CLUSTER C_USER#
11 INDEX I_USER#
select id, type, text from t1
INTERSECT
select id, type, text from t2;
2 CLUSTER C_OBJ#
3 INDEX I_OBJ#
4 TABLE TAB$
5 TABLE CLU$
6 CLUSTER C_TS#
select id, type, text from t1
MINUS
select id, type, text from t2;
ничего не возвратит
select id, type, text from t2
MINUS
select id, type, text from t1;
7 INDEX I_TS#
8 CLUSTER C_FILE#_BLOCK#
9 INDEX I_FILE#_BLOCK#
10 CLUSTER C_USER#
11 INDEX I_USER#
(select id, type, text from t1
UNION ALL
select id, type, text from t2)
MINUS
select id, type, text from t1;
7 INDEX I_TS#
8 CLUSTER C_FILE#_BLOCK#
9 INDEX I_FILE#_BLOCK#
10 CLUSTER C_USER#
11 INDEX I_USER#
Функция TRANSLATE()
select TRANSLATE('TOM KYTE', 'ABCDEFGH', 'АБЦДЕФГХ') from dual;
TOM KYTЕ
select TRANSLATE('SCOTT URMAN', 'ABCDEFGH', 'АБЦДЕФГХ') from dual
SЦOTT URMАN
select id, type, TRANSLATE(text, 'ABCDEFGH', 'АБЦДЕФГХ') from t1
2 CLUSTER Ц_OБJ#
3 INDEX I_OБJ#
4 TABLE TАБ$
5 TABLE ЦLU$
6 CLUSTER Ц_TS#
Функция DECODE:
SELECT
id,
DECODE (id,
2, 'Кластер',
3, 'Индекс',
4, 'Таблица',
5, 'Таблица',
6, 'Кластер',
'Тип неопределен') type ,
text
FROM t1;
2 Кластер C_OBJ#
3 Индекс I_OBJ#
4 Таблица TAB$
5 Таблица CLU$
6 Кластер C_TS#
Функция CASE:
SELECT id,
CASE id
WHEN 2 THEN 'Кластер'
WHEN 3 THEN 'Индекс'
WHEN 4 THEN 'Таблица'
WHEN 5 THEN 'Таблица'
WHEN 6 THEN 'Кластер'
ELSE 'Тип неопределен'
END type,
text
FROM t1;
2 Кластер C_OBJ#
3 Индекс I_OBJ#
4 Таблица TAB$
5 Таблица CLU$
6 Кластер C_TS#
или так
SELECT id,
CASE
WHEN id = 2 THEN 'Кластер'
WHEN id = 3 THEN 'Индекс'
WHEN id = 4 THEN 'Таблица'
WHEN id = 5 THEN 'Таблица'
WHEN id = 6 THEN 'Кластер'
ELSE 'Тип неопределен'
END type,
text
FROM t1;
2 Кластер C_OBJ#
3 Индекс I_OBJ#
4 Таблица TAB$
5 Таблица CLU$
6 Кластер C_TS#
Фраза WITH и вынесенные подзапросы:
Общий вид такой:
WITH
a AS (select ....from t)
,b AS (select ....from a)
,c AS (select ....from a, b)
select ... from a, b, c;
Например:
drop table t
create table t as
WITH X AS
(
SELECT 1 ID, NULL PARENT_ID, 'Игорь' F_NAME, 'Директор' TITLE, 800000 SALARY FROM dual UNION ALL
SELECT 2 ID, 1 PARENT_ID, 'Сергей' F_NAME, 'Менеджер по продажам' TITLE, 600000 SALARY FROM dual UNION ALL
SELECT 3 ID, 2 PARENT_ID, 'Андрей' F_NAME, 'Продавец' TITLE, 200000 SALARY FROM dual UNION ALL
SELECT 4 ID, 1 PARENT_ID, 'Виталий' F_NAME, 'Менеджер' TITLE, 500000 SALARY FROM dual UNION ALL
SELECT 5 ID, 2 PARENT_ID, 'Александр' F_NAME, 'Продавец' TITLE, 40000 SALARY FROM dual UNION ALL
SELECT 6 ID, 4 PARENT_ID, 'Владимир' F_NAME, 'Персонал по поддержке' TITLE, 45000 SALARY FROM dual UNION ALL
SELECT 7 ID, 4 PARENT_ID, 'Николай' F_NAME, 'Менеджер по поддержке' TITLE, 30000 SALARY FROM dual UNION ALL
SELECT 8 ID, 7 PARENT_ID, 'Ольга' F_NAME, 'Персонал по поддержке' TITLE, 29000 SALARY FROM dual UNION ALL
SELECT 9 ID, 6 PARENT_ID, 'Марина' F_NAME, 'Персонал по поддержке' TITLE, 30000 SALARY FROM dual UNION ALL
SELECT 10 ID, 1 PARENT_ID, 'Елена' F_NAME, 'Операционный менеджер' TITLE, 100000 SALARY FROM dual UNION ALL
SELECT 11 ID, 10 PARENT_ID, 'Надежда' F_NAME, 'Операционистка' TITLE, 50000 SALARY FROM dual UNION ALL
SELECT 12 ID, 10 PARENT_ID, 'Анна' F_NAME, 'Операционистка' TITLE, 45000 SALARY FROM dual UNION ALL
SELECT 13 ID, 10 PARENT_ID, 'Татьяна' F_NAME, 'Операционистка' TITLE, 47000 SALARY FROM dual
)
select * from x;
1 Игорь Директор 800000
2 1 Сергей Менеджер по продажам 600000
3 2 Андрей Продавец 200000
4 1 Виталий Менеджер 500000
5 2 Александр Продавец 40000
6 4 Владимир Персонал по поддержке 45000
7 4 Николай Менеджер по поддержке 30000
8 7 Ольга Персонал по поддержке 29000
9 6 Марина Персонал по поддержке 30000
10 1 Елена Операционный менеджер 100000
11 10 Надежда Операционистка 50000
12 10 Анна Операционистка 45000
13 10 Татьяна Операционистка 47000
Иерархические запросы
Для выполнения иерархических запросов можно использовать фразы:
CONNECT BY и START WITH оператора SELECT
SELECT [LEVEL], столбец, ...
FROM таблица
[WHERE ...]
[ [START WITH стартовое условие] [CONNECT BY PRIOR условие_prior] ]
LEVEL - уровень вложенности узлов
1 - корневой узел
Стартовое условие - с какого места начинать иерархический запрос
например ID = 1
Условие prior определяет отношение между родительскими и подчиненными строками
у нас это условие такое ID = PARENT_ID
select * from t
start with id =1
connect by prior id = parent_id;
1 Игорь Директор 800000
2 1 Сергей Менеджер по продажам 600000
3 2 Андрей Продавец 200000
5 2 Александр Продавец 40000
4 1 Виталий Менеджер 500000
6 4 Владимир Персонал по поддержке 45000
9 6 Марина Персонал по поддержке 30000
7 4 Николай Менеджер по поддержке 30000
8 7 Ольга Персонал по поддержке 29000
10 1 Елена Операционный менеджер 100000
11 10 Надежда Операционистка 50000
12 10 Анна Операционистка 45000
13 10 Татьяна Операционистка 47000
Для удобства используем псевдостолбец LEVEL
select level, t.* from t
start with id =1
connect by prior id = parent_id;
level id
1 1 Игорь Директор 800000
2 2 1 Сергей Менеджер по продажам 600000
3 3 2 Андрей Продавец 200000
3 5 2 Александр Продавец 40000
2 4 1 Виталий Менеджер 500000
3 6 4 Владимир Персонал по поддержке 45000
4 9 6 Марина Персонал по поддержке 30000
3 7 4 Николай Менеджер по поддержке 30000
4 8 7 Ольга Персонал по поддержке 29000
2 10 1 Елена Операционный менеджер 100000
3 11 10 Надежда Операционистка 50000
3 12 10 Анна Операционистка 45000
3 13 10 Татьяна Операционистка 47000
Сколько уровней в дереве:
select count(distinct level) from t
start with id =1
connect by prior id = parent_id;
4
Форматирование результатов иерархического запроса
используем функцию LPAD()
она слева дополняет значение заданными символами
LPAD(' ', 2*LEVEL-1 ) - вставляет 2*LEVEL-1 пробелов
select level,
lpad(' ', 4*level-1) || F_NAME
from t
start with id =1
connect by prior id = parent_id;
1 Игорь
2 Сергей
3 Андрей
3 Александр
2 Виталий
3 Владимир
4 Марина
3 Николай
4 Ольга
2 Елена
3 Надежда
3 Анна
3 Татьяна
Можно начать не с корневого узла
например так:
select level,
lpad(' ', 4*level-1) || F_NAME
from t
start with f_name = 'Елена'
connect by prior id = parent_id;
1 Елена
2 Надежда
2 Анна
2 Татьяна
или так:
select level,
lpad(' ', 4*level-1) || F_NAME
from t
start with id = 4
connect by prior id = parent_id;
1 Виталий
2 Владимир
3 Марина
2 Николай
3 Ольга
Во фразе START WITH можно использовать и подзапросы.
select level,
lpad(' ', 4*level-1) || F_NAME
from t
start with id = (select id from t where f_name = 'Елена')
connect by prior id = parent_id;
1 Елена
2 Надежда
2 Анна
2 Татьяна
Восходящий обход дерева
Можно вести обход дерева снизу вверх
для этого поменяем местами в условии CONNECT BY PRIOR столбцы:
connect by prior id = parent_id
на
connect by prior parent_id = id;
и в start with указать с какого места начинать.
select level,
lpad(' ', 4*level-1) || F_NAME
from t
start with id = (select id from t where f_name = 'Ольга')
connect by prior parent_id = id;
1 Ольга
2 Николай
3 Виталий
4 Игорь
Исключение из иерархического запроса узлов и ветвей
Исключим из результатов данные о служащем Сергее.
select level,
lpad(' ', 4*level-1) || F_NAME
from t
where f_name != 'Сергей'
start with id =1
connect by prior id = parent_id;
1 Игорь
3 Андрей
3 Александр
2 Виталий
3 Владимир
4 Марина
3 Николай
4 Ольга
2 Елена
3 Надежда
3 Анна
3 Татьяна
Сергей исключен, но его подчиненные Андрей и Александр остались.
Чтобы исключить всю ветвь, нужно добавить во фразу
CONNECT BY PRIOR фоазу AND:
select level,
lpad(' ', 4*level-1) || F_NAME
from t
start with id =1
connect by prior id = parent_id
and f_name != 'Сергей';
1 Игорь
2 Виталий
3 Владимир
4 Марина
3 Николай
4 Ольга
2 Елена
3 Надежда
3 Анна
3 Татьяна
Включение в иерархический запрос других условий:
например вывести только тех служащих, зарплата которых не превышает 50000
select level,
lpad(' ', 4*level-1) || F_NAME,
salary
from t
where salary <= 50000
start with id =1
connect by prior id = parent_id;
3 Александр 40000
3 Владимир 45000
4 Марина 30000
3 Николай 30000
4 Ольга 29000
3 Надежда 50000
3 Анна 45000
3 Татьяна 47000
Группировка результатов:
drop table t;
create table t
as
select object_id id, owner own, object_type type, object_name text
from all_objects
where object_type in
('SEQUENCE',
'PROCEDURE',
'PACKAGE',
'TRIGGER',
'TABLE',
'INDEX',
'SYNONYM',
'VIEW',
'FUNCTION')
and owner in ('SYS','SYSTEM');
select type, count(text) from t
group by type
order by type;
FUNCTION 117
INDEX 1460
PACKAGE 721
PROCEDURE 151
SEQUENCE 180
SYNONYM 27
TABLE 1443
TRIGGER 13
VIEW 5742
select type, count(text) from t
group by rollup(type)
order by type;
FUNCTION 117
INDEX 1460
PACKAGE 721
PROCEDURE 151
SEQUENCE 180
SYNONYM 27
TABLE 1443
TRIGGER 13
VIEW 5742
9854
Передача в ROLLUP нескольких столбцов
select own, type, count(text) from t
group by rollup(own, type)
order by own, type;
SYS FUNCTION 113
SYS INDEX 1224
SYS PACKAGE 720
SYS PROCEDURE 150
SYS SEQUENCE 158
SYS SYNONYM 19
SYS TABLE 1265
SYS TRIGGER 11
SYS VIEW 5728
SYS 9388
SYSTEM FUNCTION 4
SYSTEM INDEX 236
SYSTEM PACKAGE 1
SYSTEM PROCEDURE 1
SYSTEM SEQUENCE 22
SYSTEM SYNONYM 8
SYSTEM TABLE 178
SYSTEM TRIGGER 2
SYSTEM VIEW 14
SYSTEM 466
9854
Изменим порядок столбцов в rollup:
select own, type, count(text) from t
group by rollup(type, own)
order by type, own ;
SYS FUNCTION 113
SYSTEM FUNCTION 4
FUNCTION 117
SYS INDEX 1224
SYSTEM INDEX 236
INDEX 1460
SYS PACKAGE 720
SYSTEM PACKAGE 1
PACKAGE 721
SYS PROCEDURE 150
SYSTEM PROCEDURE 1
PROCEDURE 151
SYS SEQUENCE 158
SYSTEM SEQUENCE 22
SEQUENCE 180
SYS SYNONYM 19
SYSTEM SYNONYM 8
SYNONYM 27
SYS TABLE 1265
SYSTEM TABLE 178
TABLE 1443
SYS TRIGGER 11
SYSTEM TRIGGER 2
TRIGGER 13
SYS VIEW 5728
SYSTEM VIEW 14
VIEW 5742
9854
Фраза CUBE расширяет GROUP BY в том плане, что она возвращает строки,
содержащие предварительные итоги для всех комбинаций столбцов,
включенных во фразу CUBE, а в конце возвращается строка с итогом.
select own, type, count(text) from t
group by cube(own, type)
order by own, type;
SYS FUNCTION 113
SYS INDEX 1224
SYS PACKAGE 720
SYS PROCEDURE 150
SYS SEQUENCE 158
SYS SYNONYM 19
SYS TABLE 1265
SYS TRIGGER 11
SYS VIEW 5728
SYS 9388
SYSTEM FUNCTION 4
SYSTEM INDEX 236
SYSTEM PACKAGE 1
SYSTEM PROCEDURE 1
SYSTEM SEQUENCE 22
SYSTEM SYNONYM 8
SYSTEM TABLE 178
SYSTEM TRIGGER 2
SYSTEM VIEW 14
SYSTEM 466
FUNCTION 117
INDEX 1460
PACKAGE 721
PROCEDURE 151
SEQUENCE 180
SYNONYM 27
TABLE 1443
TRIGGER 13
VIEW 5742
9854
Изменим порядок столбцов в cube()
select own, type, count(text) from t
group by cube(type, own)
order by type, own;
SYS FUNCTION 113
SYSTEM FUNCTION 4
FUNCTION 117
SYS INDEX 1224
SYSTEM INDEX 236
INDEX 1460
SYS PACKAGE 720
SYSTEM PACKAGE 1
PACKAGE 721
SYS PROCEDURE 150
SYSTEM PROCEDURE 1
PROCEDURE 151
SYS SEQUENCE 158
SYSTEM SEQUENCE 22
SEQUENCE 180
SYS SYNONYM 19
SYSTEM SYNONYM 8
SYNONYM 27
SYS TABLE 1265
SYSTEM TABLE 178
TABLE 1443
SYS TRIGGER 11
SYSTEM TRIGGER 2
TRIGGER 13
SYS VIEW 5728
SYSTEM VIEW 14
VIEW 5742
SYS 9388
SYSTEM 466
9854
Функция GROUPING() принимает столбец, а возвращает 0 или 1
Если значение столбца null, то возвращает 1
если значение столбца не пустое, то возвращает 0
Это может пригодиться:
select type, count(text) from t
group by cube(type)
order by type;
FUNCTION 117
INDEX 1460
PACKAGE 721
PROCEDURE 151
SEQUENCE 180
SYNONYM 27
TABLE 1443
TRIGGER 13
VIEW 5742
9854
select grouping(type), type, count(text) from t
group by cube(type)
order by type;
0 FUNCTION 117
0 INDEX 1460
0 PACKAGE 721
0 PROCEDURE 151
0 SEQUENCE 180
0 SYNONYM 27
0 TABLE 1443
0 TRIGGER 13
0 VIEW 5742
1 9854
select
case grouping(type)
when 1 then 'Итог:'
else type
end as type,
count(text) from t
group by cube(type)
order by type;
FUNCTION 117
INDEX 1460
PACKAGE 721
PROCEDURE 151
SEQUENCE 180
SYNONYM 27
TABLE 1443
TRIGGER 13
VIEW 5742
Итог: 9854
Конвертация значений нескольких столбцов:
select
case grouping(own)
when 1 then 'Все own : '
else own
end as own,
case grouping(type)
when 1 then 'Все type : '
else type
end as type,
count(text) from t
group by rollup(own, type)
order by own, type;
SYS FUNCTION 113
SYS INDEX 1224
SYS PACKAGE 720
SYS PROCEDURE 150
SYS SEQUENCE 158
SYS SYNONYM 19
SYS TABLE 1265
SYS TRIGGER 11
SYS VIEW 5728
SYS Все type : 9388
SYSTEM FUNCTION 4
SYSTEM INDEX 236
SYSTEM PACKAGE 1
SYSTEM PROCEDURE 1
SYSTEM SEQUENCE 22
SYSTEM SYNONYM 8
SYSTEM TABLE 178
SYSTEM TRIGGER 2
SYSTEM VIEW 14
SYSTEM Все type : 466
Все own : Все type : 9854
С использованием фразы cube:
select
case grouping(own)
when 1 then 'Все own : '
else own
end as own,
case grouping(type)
when 1 then 'Все type : '
else type
end as type,
count(text) from t
group by cube(own, type)
order by own, type;
SYS FUNCTION 113
SYS INDEX 1224
SYS PACKAGE 720
SYS PROCEDURE 150
SYS SEQUENCE 158
SYS SYNONYM 19
SYS TABLE 1265
SYS TRIGGER 11
SYS VIEW 5728
SYS Все type : 9388
SYSTEM FUNCTION 4
SYSTEM INDEX 236
SYSTEM PACKAGE 1
SYSTEM PROCEDURE 1
SYSTEM SEQUENCE 22
SYSTEM SYNONYM 8
SYSTEM TABLE 178
SYSTEM TRIGGER 2
SYSTEM VIEW 14
SYSTEM Все type : 466
Все own : FUNCTION 117
Все own : INDEX 1460
Все own : PACKAGE 721
Все own : PROCEDURE 151
Все own : SEQUENCE 180
Все own : SYNONYM 27
Все own : TABLE 1443
Все own : TRIGGER 13
Все own : VIEW 5742
Все own : Все type : 9854
Если необходимо получить только промежуточные итоги, используйте фразу
GROUPING SETS
select own, type, count(text) from t
group by grouping sets(own, type)
order by own, type;
SYS 9388
SYSTEM 466
FUNCTION 117
INDEX 1460
PACKAGE 721
PROCEDURE 151
SEQUENCE 180
SYNONYM 27
TABLE 1443
TRIGGER 13
VIEW 5742
Ранее мы рассмотрели функцию grouping()
она работала так:
grouping(null) -> 1
grouping(not null) -> 0
Существует функция grouping_id()
она принимает два аргумента и возвращает следующие значения:
grouping_id(null, null) -> 0
grouping_id(null, not null) -> 1
grouping_id(not null, null) -> 2
grouping_id(not null, not null) -> 3
Где это может быть полезно ?
select own, type,
grouping(own) as own_grp,
grouping(type) as type_grp,
grouping_id(own, type) as grp_id,
count(text) from t
group by cube(own, type)
order by own, type;
SYS FUNCTION 0 0 0 113
SYS INDEX 0 0 0 1224
SYS PACKAGE 0 0 0 720
SYS PROCEDURE 0 0 0 150
SYS SEQUENCE 0 0 0 158
SYS SYNONYM 0 0 0 19
SYS TABLE 0 0 0 1265
SYS TRIGGER 0 0 0 11
SYS VIEW 0 0 0 5728
SYS 0 1 1 9388
SYSTEM FUNCTION 0 0 0 4
SYSTEM INDEX 0 0 0 236
SYSTEM PACKAGE 0 0 0 1
SYSTEM PROCEDURE 0 0 0 1
SYSTEM SEQUENCE 0 0 0 22
SYSTEM SYNONYM 0 0 0 8
SYSTEM TABLE 0 0 0 178
SYSTEM TRIGGER 0 0 0 2
SYSTEM VIEW 0 0 0 14
SYSTEM 0 1 1 466
FUNCTION 1 0 2 117
INDEX 1 0 2 1460
PACKAGE 1 0 2 721
PROCEDURE 1 0 2 151
SEQUENCE 1 0 2 180
SYNONYM 1 0 2 27
TABLE 1 0 2 1443
TRIGGER 1 0 2 13
VIEW 1 0 2 5742
1 1 3 9854
Одно из полезных применений функции grouping_id() - это фильтрация строк при помощи фразы HAVING.
select own, type,
grouping_id(own, type) as grp_id,
count(text) from t
group by cube(own, type)
having grouping_id(own, type) > 0
order by own, type;
SYS 1 9388
SYSTEM 1 466
FUNCTION 2 117
INDEX 2 1460
PACKAGE 2 721
PROCEDURE 2 151
SEQUENCE 2 180
SYNONYM 2 27
TABLE 2 1443
TRIGGER 2 13
VIEW 2 5742
3 9854
Еще примеры:
Рассмотрим такой запрос:
select own, type, count(text) from t
group by own, rollup(own, type);
SYS VIEW 5728
SYS INDEX 1224
SYS TABLE 1265
SYS PACKAGE 720
SYS SYNONYM 19
SYS TRIGGER 11
SYS FUNCTION 113
SYS SEQUENCE 158
SYS PROCEDURE 150
SYSTEM VIEW 14
SYSTEM INDEX 236
SYSTEM TABLE 178
SYSTEM PACKAGE 1
SYSTEM SYNONYM 8
SYSTEM TRIGGER 2
SYSTEM FUNCTION 4
SYSTEM SEQUENCE 22
SYSTEM PROCEDURE 1
SYS 9388
SYSTEM 466
SYS 9388
SYSTEM 466
Здесь мы во фразе group by дважды сгруппировали
сначала по столбцу own, а затем по фразе rollup()
Но последние две строки выходных данных, дублируют предыдущие.
Чтобы это исправить, воспользуемся функцией group_id()
Функция group_id() не принимает никаких параметров.
Если в какой либо конкретной группировке есть n дубликатов, group_id() возвратит числа в диапазоне от 0 до n-1.
select own, type, group_id(), count(text) from t
group by own, rollup(own, type);
SYS VIEW 0 5728
SYS INDEX 0 1224
SYS TABLE 0 1265
SYS PACKAGE 0 720
SYS SYNONYM 0 19
SYS TRIGGER 0 11
SYS FUNCTION 0 113
SYS SEQUENCE 0 158
SYS PROCEDURE 0 150
SYSTEM VIEW 0 14
SYSTEM INDEX 0 236
SYSTEM TABLE 0 178
SYSTEM PACKAGE 0 1
SYSTEM SYNONYM 0 8
SYSTEM TRIGGER 0 2
SYSTEM FUNCTION 0 4
SYSTEM SEQUENCE 0 22
SYSTEM PROCEDURE 0 1
SYS 0 9388
SYSTEM 0 466
SYS 1 9388
SYSTEM 1 466
Теперь две последние строки можно отфильтровать во фразе having:
select own, type, group_id(), count(text) from t
group by own, rollup(own, type)
having group_id() = 0;
SYS VIEW 0 5728
SYS INDEX 0 1224
SYS TABLE 0 1265
SYS PACKAGE 0 720
SYS SYNONYM 0 19
SYS TRIGGER 0 11
SYS FUNCTION 0 113
SYS SEQUENCE 0 158
SYS PROCEDURE 0 150
SYSTEM VIEW 0 14
SYSTEM INDEX 0 236
SYSTEM TABLE 0 178
SYSTEM PACKAGE 0 1
SYSTEM SYNONYM 0 8
SYSTEM TRIGGER 0 2
SYSTEM FUNCTION 0 4
SYSTEM SEQUENCE 0 22
SYSTEM PROCEDURE 0 1
SYS 0 9388
SYSTEM 0 466
