笔记：Oracle SQL 高级查询简介 (1) case、层次化、扩展group by
1. 集合操作
union all 、 union、 intersect 、 minus
注意：集合操作的所有查询返回的列数、列类型必须相同，但是列名可以不一样。
2、translate 函数
translate(x, str1, str2) . 在字段x 中查找str1中的字符，转化为str2中对应的字符。
例子：
select translate(x, 'abc', 'xyz') from table1
对字段x中字符进行替换：a->x, b->y, x->z
3.DECODE() 函数
就是case 语句。
4、CASE 语句
搜索case表达式 例子：
select e.employee_id,
case when e.salary>=10000 then 'good'
when e.salary>5000 and e.salary < 10000 then 'middle'
else 'poor'
end as salary_type
from hr.employees e
结果：
EMPLOYEE_ID SALARY_TYPE
----------- -----------
100 good
101 good
102 good
103 middle
104 middle
105 poor
106 poor
107 poor
108 good
简单case表达式 例子：
select e.employee_id , case e.department_id when 90 then 'research' when 60 then 'sales' else 'unkown' end as department from hr.employees e
结果：
EMPLOYEE_ID DEPARTMENT
----------- ----------
100 research
101 research
102 research
103 sales
104 sales
105 sales
106 sales
107 sales
108 unkown
5、层次化查询
使用 start with 和 connect to privor
select level , e.employee_id , e.manager_id, e.first_name||' '|| e.last_name as full_name from hr.employees e start with e.employee_id = 100 connect by prior e.employee_id = e.manager_id order by level
结果：
LEVEL EMPLOYEE_ID MANAGER_ID FULL_NAME
---------- ----------- ---------- ----------------------------------------------
1 100 Steven King
2 102 100 Lex De Haan
2 114 100 Den Raphaely
2 120 100 Matthew Weiss
2 121 100 Adam Fripp
2 122 100 Payam Kaufling
2 123 100 Shanta Vollman
2 124 100 Kevin Mourgos
2 145 100 John Russell
2 146 100 Karen Partners
2 147 100 Alberto Errazuriz
2 148 100 Gerald Cambrault
2 149 100 Eleni Zlotkey
2 201 100 Michael Hartstein
2 101 100 Neena Kochhar
3 108 101 Nancy Greenberg
3 200 101 Jennifer Whalen
3 203 101 Susan Mavris
3 204 101 Hermann Baer
3 205 101 Shelley Higgins
添加过滤条件：
如
select level , e.employee_id , e.manager_id, e.first_name||' '|| e.last_name as full_name from hr.employees e where e.last_name != 'Kochhar' start with e.employee_id = 100 connect by prior e.employee_id = e.manager_id order by level
以上语句滤除了Kochhar这个人(id=101)，但是Kochhar的下属还会出现在结果中：
LEVEL EMPLOYEE_ID MANAGER_ID FULL_NAME
---------- ----------- ---------- ----------------------------------------------
1 100 Steven King
2 114 100 Den Raphaely
2 120 100 Matthew Weiss
2 121 100 Adam Fripp
2 122 100 Payam Kaufling
2 201 100 Michael Hartstein
2 124 100 Kevin Mourgos
2 145 100 John Russell
2 146 100 Karen Partners
2 147 100 Alberto Errazuriz
2 148 100 Gerald Cambrault
2 149 100 Eleni Zlotkey
2 102 100 Lex De Haan
2 123 100 Shanta Vollman
3 108 101 Nancy Greenberg
3 200 101 Jennifer Whalen
3 203 101 Susan Mavris
3 204 101 Hermann Baer
3 205 101 Shelley Higgins
3 103 102 Alexander Hunold
要一起滤除John 的下属，必须这样写
select level , e.employee_id , e.manager_id, e.first_name||' '|| e.last_name as full_name from hr.employees e start with e.employee_id = 100 connect by prior e.employee_id = e.manager_id and e.last_name != 'Kochhar' order by level
这样结果为：
LEVEL EMPLOYEE_ID MANAGER_ID FULL_NAME
---------- ----------- ---------- ----------------------------------------------
1 100 Steven King
2 114 100 Den Raphaely
2 120 100 Matthew Weiss
2 121 100 Adam Fripp
2 122 100 Payam Kaufling
2 201 100 Michael Hartstein
2 124 100 Kevin Mourgos
2 145 100 John Russell
2 146 100 Karen Partners
2 147 100 Alberto Errazuriz
2 148 100 Gerald Cambrault
2 149 100 Eleni Zlotkey
2 102 100 Lex De Haan
2 123 100 Shanta Vollman
3 103 102 Alexander Hunold
3 115 114 Alexander Khoo
3 116 114 Shelli Baida
3 117 114 Sigal Tobias
3 118 114 Guy Himuro
3 119 114 Karen Colmenares
6. 扩展的Group By子句
(1). ROLLUP子句
为每一个分组返回一条记录，并为全部分组返回总计。
select e.department_id , avg(e.salary) from hr.employees e where e.department_id is not null group by rollup(e.department_id) ;
结果：
DEPARTMENT_ID AVG(E.SALARY)
------------- -------------
10 4400
20 9500
30 4150
40 6500
50 3475.55555555
60 5760
70 10000
80 8955.88235294
90 19333.3333333
100 8601.33333333
110 10154
6456.75471698
12 rows selected
可以在多列上统计
select e.department_id , e.job_id, avg(e.salary) from hr.employees e where e.department_id is not null and e.job_id is not null group by rollup(e.department_id, e.job_id) ;
结果：
DEPARTMENT_ID JOB_ID AVG(E.SALARY)
------------- ---------- -------------
10 AD_ASST 4400
10 4400
20 MK_MAN 13000
20 MK_REP 6000
20 9500
30 PU_MAN 11000
30 PU_CLERK 2780
30 4150
40 HR_REP 6500
40 6500
50 ST_MAN 7280
50 SH_CLERK 3215
50 ST_CLERK 2785
50 3475.55555555
60 IT_PROG 5760
60 5760
70 PR_REP 10000
70 10000
80 SA_MAN 12200
80 SA_REP 8396.55172413
80 8955.88235294
90 AD_VP 17000
90 AD_PRES 24000
90 19333.3333333
100 FI_MGR 12008
100 FI_ACCOUNT 7920
100 8601.33333333
110 AC_MGR 12008
110 AC_ACCOUNT 8300
110 10154
6456.75471698
31 rows selected
(2).CUBE
为每一个分组返回一条记录，并为全部小组组合返回总计，并给出统计。
select e.department_id , e.job_id, avg(e.salary) from hr.employees e where e.department_id is not null and e.job_id is not null group by cube(e.department_id, e.job_id) order by e.department_id, e.job_id;
结果：
DEPARTMENT_ID JOB_ID AVG(E.SALARY)
------------- ---------- -------------
10 AD_ASST 4400
10 4400
20 MK_MAN 13000
20 MK_REP 6000
20 9500
30 PU_CLERK 2780
30 PU_MAN 11000
30 4150
40 HR_REP 6500
40 6500
50 SH_CLERK 3215
50 ST_CLERK 2785
50 ST_MAN 7280
50 3475.55555555
60 IT_PROG 5760
60 5760
70 PR_REP 10000
70 10000
80 SA_MAN 12200
80 SA_REP 8396.55172413
80 8955.88235294
90 AD_PRES 24000
90 AD_VP 17000
90 19333.3333333
100 FI_ACCOUNT 7920
100 FI_MGR 12008
100 8601.33333333
110 AC_ACCOUNT 8300
110 AC_MGR 12008
110 10154
AC_ACCOUNT 8300
AC_MGR 12008
AD_ASST 4400
AD_PRES 24000
AD_VP 17000
FI_ACCOUNT 7920
FI_MGR 12008
HR_REP 6500
IT_PROG 5760
MK_MAN 13000
MK_REP 6000
PR_REP 10000
PU_CLERK 2780
PU_MAN 11000
SA_MAN 12200
SA_REP 8396.55172413
SH_CLERK 3215
ST_CLERK 2785
ST_MAN 7280
6456.75471698
50 rows selected
(3). GROUPING 函数
接收一列，列为空则返回1，非空则返回0.
select grouping(e.department_id), e.department_id , avg(e.salary) from hr.employees e where e.department_id is not null group by rollup(e.department_id)
结果：
GROUPING(E.DEPARTMENT_ID) DEPARTMENT_ID AVG(E.SALARY)
------------------------- ------------- -------------
0 10 4400
0 20 9500
0 30 4150
0 40 6500
0 50 3475.55555555
0 60 5760
0 70 10000
0 80 8955.88235294
0 90 19333.3333333
0 100 8601.33333333
0 110 10154
1 6456.75471698
12 rows selected
与case when 配合：
select case when grouping(e.department_id)=0 then ''||e.department_id else 'All Departments' end as department_id , case when grouping(e.job_id)=0 then e.job_id else 'All Jobs' end as job_id, avg(e.salary) from hr.employees e where e.department_id is not null and e.job_id is not null group by cube(e.department_id, e.job_id) order by e.department_id, e.job_id;
结果：
DEPARTMENT_ID JOB_ID AVG(E.SALARY) ---------------------------------------- ---------- ------------- 10 AD_ASST 4400 10 All Jobs 4400 20 MK_MAN 13000 20 MK_REP 6000 20 All Jobs 9500 30 PU_CLERK 2780 30 PU_MAN 11000 30 All Jobs 4150 40 HR_REP 6500 40 All Jobs 6500 50 SH_CLERK 3215 50 ST_CLERK 2785 50 ST_MAN 7280 50 All Jobs 3475.55555555 60 IT_PROG 5760 60 All Jobs 5760 70 PR_REP 10000 70 All Jobs 10000 80 SA_MAN 12200 80 SA_REP 8396.55172413 80 All Jobs 8955.88235294 90 AD_PRES 24000 90 AD_VP 17000 90 All Jobs 19333.3333333 100 FI_ACCOUNT 7920 100 FI_MGR 12008 100 All Jobs 8601.33333333 110 AC_ACCOUNT 8300 110 AC_MGR 12008 110 All Jobs 10154 All Departments AC_ACCOUNT 8300 All Departments AC_MGR 12008 All Departments AD_ASST 4400 All Departments AD_PRES 24000 All Departments AD_VP 17000 All Departments FI_ACCOUNT 7920 All Departments FI_MGR 12008 All Departments HR_REP 6500 All Departments IT_PROG 5760 All Departments MK_MAN 13000 All Departments MK_REP 6000 All Departments PR_REP 10000 All Departments PU_CLERK 2780 All Departments PU_MAN 11000 All Departments SA_MAN 12200 All Departments SA_REP 8396.55172413 All Departments SH_CLERK 3215 All Departments ST_CLERK 2785 All Departments ST_MAN 7280 All Departments All Jobs 6456.75471698 50 rows selected
(4). GROUPING SETS
grouping sets 只返回小计信息。
select e.department_id , e.job_id, avg(e.salary) from hr.employees e where e.department_id is not null and e.job_id is not null group by grouping sets(e.department_id, e.job_id) order by e.department_id, e.job_id;
结果：
DEPARTMENT_ID JOB_ID AVG(E.SALARY)
------------- ---------- -------------
10 4400
20 9500
30 4150
40 6500
50 3475.55555555
60 5760
70 10000
80 8955.88235294
90 19333.3333333
100 8601.33333333
110 10154
AC_ACCOUNT 8300
AC_MGR 12008
AD_ASST 4400
AD_PRES 24000
AD_VP 17000
FI_ACCOUNT 7920
FI_MGR 12008
HR_REP 6500
IT_PROG 5760
MK_MAN 13000
MK_REP 6000
PR_REP 10000
PU_CLERK 2780
PU_MAN 11000
SA_MAN 12200
SA_REP 8396.55172413
SH_CLERK 3215
ST_CLERK 2785
ST_MAN 7280
30 rows selected
(5).GROUPING_ID 函数
相当于钱几个列的GROUPING值的组合。以下SQL
select grouping(e.department_id), grouping(e.job_id), grouping_id(e.department_id, e.job_id) as my_grouping_id, e.department_id , e.job_id, avg(e.salary) from hr.employees e where e.department_id is not null and e.job_id is not null group by cube(e.department_id, e.job_id) order by e.department_id, e.job_id;
返回：
GROUPING(E.DEPARTMENT_ID) GROUPING(E.JOB_ID) MY_GROUPING_ID DEPARTMENT_ID JOB_ID AVG(E.SALARY)
------------------------- ------------------ -------------- ------------- ---------- -------------
0 0 0 10 AD_ASST 4400
0 1 1 10 4400
0 0 0 20 MK_MAN 13000
0 0 0 20 MK_REP 6000
0 1 1 20 9500
0 0 0 30 PU_CLERK 2780
0 0 0 30 PU_MAN 11000
0 1 1 30 4150
0 0 0 40 HR_REP 6500
0 1 1 40 6500
0 0 0 50 SH_CLERK 3215
0 0 0 50 ST_CLERK 2785
0 0 0 50 ST_MAN 7280
0 1 1 50 3475.55555555
0 0 0 60 IT_PROG 5760
0 1 1 60 5760
0 0 0 70 PR_REP 10000
0 1 1 70 10000
0 0 0 80 SA_MAN 12200
0 0 0 80 SA_REP 8396.55172413
0 1 1 80 8955.88235294
0 0 0 90 AD_PRES 24000
0 0 0 90 AD_VP 17000
0 1 1 90 19333.3333333
0 0 0 100 FI_ACCOUNT 7920
0 0 0 100 FI_MGR 12008
0 1 1 100 8601.33333333
0 0 0 110 AC_ACCOUNT 8300
0 0 0 110 AC_MGR 12008
0 1 1 110 10154
1 0 2 AC_ACCOUNT 8300
1 0 2 AC_MGR 12008
1 0 2 AD_ASST 4400
1 0 2 AD_PRES 24000
1 0 2 AD_VP 17000
1 0 2 FI_ACCOUNT 7920
1 0 2 FI_MGR 12008
1 0 2 HR_REP 6500
1 0 2 IT_PROG 5760
1 0 2 MK_MAN 13000
1 0 2 MK_REP 6000
1 0 2 PR_REP 10000
1 0 2 PU_CLERK 2780
1 0 2 PU_MAN 11000
1 0 2 SA_MAN 12200
1 0 2 SA_REP 8396.55172413
1 0 2 SH_CLERK 3215
1 0 2 ST_CLERK 2785
1 0 2 ST_MAN 7280
1 1 3 6456.75471698
50 rows selected
GROUPING_ID 与having 联合使用，可以过滤出想需要的统计值。
(6). GROUP_ID
在group by 中可以多次使用同一列，这样可以实现对数据的重新组织，或者按照不同的数据分组进行统计。
select e.department_id , e.job_id, avg(e.salary) from hr.employees e where e.department_id is not null and e.job_id is not null group by e.department_id, rollup(e.department_id, e.job_id)
结果：
DEPARTMENT_ID JOB_ID AVG(E.SALARY)
------------- ---------- -------------
10 AD_ASST 4400
20 MK_MAN 13000
20 MK_REP 6000
30 PU_MAN 11000
30 PU_CLERK 2780
40 HR_REP 6500
50 ST_MAN 7280
50 SH_CLERK 3215
50 ST_CLERK 2785
60 IT_PROG 5760
70 PR_REP 10000
80 SA_MAN 12200
80 SA_REP 8396.55172413
90 AD_VP 17000
90 AD_PRES 24000
100 FI_MGR 12008
100 FI_ACCOUNT 7920
110 AC_MGR 12008
110 AC_ACCOUNT 8300
10 4400
20 9500
30 4150
40 6500
50 3475.55555555
60 5760
70 10000
80 8955.88235294
90 19333.3333333
100 8601.33333333
110 10154
10 4400
20 9500
30 4150
40 6500
50 3475.55555555
60 5760
70 10000
80 8955.88235294
90 19333.3333333
100 8601.33333333
110 10154
结果中有重复的分组，可以使用GROUP_ID消除。
group_id 不接受任何参数，如果某个特定的分组出现n次，那么grouo_id返回从0到n-1之间的整数。
如改写以上SQL为：
select e.department_id , e.job_id, group_id(), avg(e.salary) from hr.employees e where e.department_id is not null and e.job_id is not null group by e.department_id, rollup(e.department_id, e.job_id)
结果为：
DEPARTMENT_ID JOB_ID GROUP_ID() AVG(E.SALARY)
------------- ---------- ---------- -------------
10 AD_ASST 0 4400
20 MK_MAN 0 13000
20 MK_REP 0 6000
30 PU_MAN 0 11000
30 PU_CLERK 0 2780
40 HR_REP 0 6500
50 ST_MAN 0 7280
50 SH_CLERK 0 3215
50 ST_CLERK 0 2785
60 IT_PROG 0 5760
70 PR_REP 0 10000
80 SA_MAN 0 12200
80 SA_REP 0 8396.55172413
90 AD_VP 0 17000
90 AD_PRES 0 24000
100 FI_MGR 0 12008
100 FI_ACCOUNT 0 7920
110 AC_MGR 0 12008
110 AC_ACCOUNT 0 8300
10 0 4400
20 0 9500
30 0 4150
40 0 6500
50 0 3475.55555555
60 0 5760
70 0 10000
80 0 8955.88235294
90 0 19333.3333333
100 0 8601.33333333
110 0 10154
10 1 4400
20 1 9500
30 1 4150
40 1 6500
50 1 3475.55555555
60 1 5760
70 1 10000
80 1 8955.88235294
90 1 19333.3333333
100 1 8601.33333333
110 1 10154
与HAVING 子句联合使用，则可以消除重复的列。
