笔记：Oracle SQL 高级查询简介 (1) case、层次化、扩展group by
来源：互联网 发布：新威尔机械战警的编程 编辑：程序博客网 时间：2024/04/30 09:43
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
(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.7547169812 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 nullgroup 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.7547169831 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 nullgroup 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.7547169850 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.7547169812 rows selected
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 nullgroup 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 440010 All Jobs 440020 MK_MAN 1300020 MK_REP 600020 All Jobs 950030 PU_CLERK 278030 PU_MAN 1100030 All Jobs 415040 HR_REP 650040 All Jobs 650050 SH_CLERK 321550 ST_CLERK 278550 ST_MAN 728050 All Jobs 3475.5555555560 IT_PROG 576060 All Jobs 576070 PR_REP 1000070 All Jobs 1000080 SA_MAN 1220080 SA_REP 8396.55172413 80 All Jobs 8955.8823529490 AD_PRES 2400090 AD_VP 1700090 All Jobs 19333.3333333100 FI_ACCOUNT 7920100 FI_MGR 12008100 All Jobs 8601.33333333110 AC_ACCOUNT 8300110 AC_MGR 12008110 All Jobs 10154All Departments AC_ACCOUNT 8300All Departments AC_MGR 12008All Departments AD_ASST 4400All Departments AD_PRES 24000All Departments AD_VP 17000All Departments FI_ACCOUNT 7920All Departments FI_MGR 12008All Departments HR_REP 6500All Departments IT_PROG 5760All Departments MK_MAN 13000All Departments MK_REP 6000 All Departments PR_REP 10000All Departments PU_CLERK 2780All Departments PU_MAN 11000All Departments SA_MAN 12200All Departments SA_REP 8396.55172413All Departments SH_CLERK 3215All Departments ST_CLERK 2785All Departments ST_MAN 7280All Departments All Jobs 6456.7547169850 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 nullgroup 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 728030 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 nullgroup 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.7547169850 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 nullgroup 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 nullgroup 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 子句联合使用，则可以消除重复的列。
0 0
- 笔记：Oracle SQL 高级查询简介 (1) case、层次化、扩展group by
- 59.Oracle数据库SQL开发之 高级查询——使用扩展的GROUP BY子句
- Oracle高级查询，GROUP BY
- Oracle高级查询之GROUP BY
- Oracle高级查询之GROUP BY
- Oracle高级查询之GROUP BY
- Oracle高级查询之GROUP BY
- Oracle高级查询之GROUP BY
- Oracle高级查询之GROUP BY
- SQL:Oracle层次查询总结 connect by
- oracle case.when group by
- 笔记：Oracle SQL 高级查询简介 (2) 分析函数
- 傅老师课堂：Oracle高级查询之GROUP BY
- 傅老师课堂：Oracle高级查询之GROUP BY
- oracle 涉及行转列、复合查询、函数、 HAVING 、order by、group by、count、case when then、 join
- 数据库学习笔记---SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)
- 58.Oracle数据库SQL开发之 高级查询——层次化查询
- Oracle SQL层次查询、Hierarchical Queries、connecty by
- 迎春舞会之三人组舞 --dp
- NSUserDefaults读取和写入自定义对象
- scrollTo/scrollBy方法和手势监听器的使用
- Spring 创建bean的时机
- 安卓动态控制RelativeLayout的组件
- 笔记：Oracle SQL 高级查询简介 (1) case、层次化、扩展group by
- 旅行商简化版
- pat1018Public Bike Management (30)
- 单例模式之读取配置文件
- 6
- C++学习笔记之RTTI（运行时类型识别）
- VS中的快捷键须知！
- AutoCompleteTextView学习
- codeforces 180C C. Letter(dp)
