Простые SQL-запросы из таблиц Oracle

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