Oracle数据库：oracle嵌套分组函数（聚合函数），组函数的练习题，挺复杂的，用好decode函数，很有趣
2022找工作是学历、能力和运气的超强结合体，遇到寒冬，大厂不招人，可能很多算法学生都得去找开发，测开
测开的话，你就得学数据库，sql，oracle，尤其sql要学，当然，像很多金融企业、安全机构啥的，他们必须要用oracle数据库
这oracle比sql安全，强大多了，所以你需要学习，最重要的，你要是考网络警察公务员，这玩意你不会就别去报名了，耽误时间！
oracle系列文章：
【1】Oracle数据库：啥是oracle数据库？你为啥要学oracle？
【2】Oracle数据库：oracle 11g安装教程，已安装好的oracle各个文件夹的作用，oracle用户权限怎么样
【3】Oracle数据库：oracle启动，oracle客户端工具plsql安装教程和使用方法
【4】Oracle数据库：创建表空间，创建新用户，给用户分配对象、角色和系统权限，登录新用户建表
【5】Oracle数据库：链接配置，包括sqlnet.ora里面的transnames.ora配置数据库标识符SID，listener暂时简单了解
【6】Oracle数据库：net configureation assistant工具配置监听listener，配置本地网络访问服务器上的数据库
【7】Oracle数据库：oracle字符类型、数字类型、创建表表名的命名规则
【8】Oracle数据库：约束条件：主键约束、唯一约束、检查约束、非空约束、外键约束、默认值填写
【9】Oracle数据库：表的关系：一对多，一对一，多对多，一一继承和修改的关系，表不是重点，重点是数据和约束关系
【10】Oracle数据库：sql语言结构，数据查询语言DQL，select * from table；算术，别名，连接，去重等操作
【11】Oracle数据库：约束行限制where语句，判断条件，比较条件，字符串日期格式，in,like,escape,null语句
【12】Oracle数据库：逻辑运算and，or，not和各种运算的优先级控制
【13】Oracle数据库：排序order by语句，select from where order by的执行先后顺序，各种样例
【14】Oracle数据库：oracle函数，单行函数，多行函数，upper，lower，initcap，字符串函数
【15】Oracle数据库：数字函数，日期函数，round，trunc，mod，months_between,add_months,next_day,last_day,sysdate
【16】Oracle数据库：oracle数据类型转换to_char()日期和数字转字符,to_number()字符转数字,to_date()字符转日期函数
【17】Oracle数据库：oracle函数嵌套，nvl函数，nvl2函数，nullif函数，coalesce合并函数
【18】Oracle数据库：条件表达式case when then else end，decode函数，oracle单行函数练习示例
【19】Oracle数据库：oracle多表查询，等值连接，非等值连接，自连接的sql语句和规则
【20】Oracle数据库：oracle外连接left/right/full outer join on,oracle扩展的左右外连接展示符号(+)
【21】Oracle数据库：自然连接natural join，using语句，注意避免写交叉连接
【22】Oracle数据库：oracle内连接inner join on，多表查询各种自链接、内连接、外连接的练习示例
【23】Oracle数据库：oracle组函数，聚合函数，多行函数，avg，sum，min，max，count，group by，having
oracle嵌套组函数
按部门现实平均薪水
SQL> select e.department_id,avg(e.salary) from employees e group by department_id;
DEPARTMENT_ID AVG(E.SALARY)
------------- -------------
100 8601.33333333
30 4150
7000
90 19333.3333333
20 9500
70 10000
110 10154
50 3475.55555555
80 8955.88235294
40 6500
60 5760
10 4400
12 rows selected
最大那个好说
取最大值
SQL> select max(avg(e.salary)) from employees e group by department_id;
MAX(AVG(E.SALARY))
------------------
19333.3333333333
这就是嵌套组合函数
组函数的练习题
组函数默认是忽略null的
需要纳入null，则加nvl
先拿where，再group by
然后order by
或者having
所有雇员，对整个表操作
SQL> select min(salary) min,max(salary) max,sum(salary) sum,avg(salary) avg from employees;
MIN MAX SUM AVG
---------- ---------- ---------- ----------
2100 24000 691416 6461.83177
然后四舍五入
SQL> select job_id,min(salary) min,max(salary) max,sum(salary) sum,round(avg(salary)) avg from employees group by job_id;
JOB_ID MIN MAX SUM AVG
---------- ---------- ---------- ---------- ----------
IT_PROG 4200 9000 28800 5760
AC_MGR 12008 12008 12008 12008
AC_ACCOUNT 8300 8300 8300 8300
ST_MAN 5800 8200 36400 7280
PU_MAN 11000 11000 11000 11000
AD_ASST 4400 4400 4400 4400
AD_VP 17000 17000 34000 17000
SH_CLERK 2500 4200 64300 3215
FI_ACCOUNT 6900 9000 39600 7920
FI_MGR 12008 12008 12008 12008
PU_CLERK 2500 3100 13900 2780
SA_MAN 10500 14000 61000 12200
MK_MAN 13000 13000 13000 13000
PR_REP 10000 10000 10000 10000
AD_PRES 24000 24000 24000 24000
SA_REP 6100 11500 250500 8350
MK_REP 6000 6000 6000 6000
ST_CLERK 2100 3600 55700 2785
HR_REP 6500 6500 6500 6500
19 rows selected
好说
SQL> select job_id,count(*) peo from employees group by job_id;
JOB_ID PEO
---------- ----------
AC_ACCOUNT 1
AC_MGR 1
AD_ASST 1
AD_PRES 1
AD_VP 2
FI_ACCOUNT 5
FI_MGR 1
HR_REP 1
IT_PROG 5
MK_MAN 1
MK_REP 1
PR_REP 1
PU_CLERK 5
PU_MAN 1
SA_MAN 5
SA_REP 30
SH_CLERK 20
ST_CLERK 20
ST_MAN 5
19 rows selected
count就是统计行，行就是人数
SQL> select job_id,count(manager_id) peo from employees group by job_id;
JOB_ID PEO
---------- ----------
IT_PROG 5
AC_MGR 1
AC_ACCOUNT 1
ST_MAN 5
PU_MAN 1
AD_ASST 1
AD_VP 2
SH_CLERK 20
FI_ACCOUNT 5
FI_MGR 1
PU_CLERK 5
SA_MAN 5
MK_MAN 1
PR_REP 1
AD_PRES 0
SA_REP 30
MK_REP 1
ST_CLERK 20
HR_REP 1
19 rows selected
专门统计x列
SQL> select count(manager_id) peo from employees;
PEO
----------
106
如果不看job呢
我们看看要不要去重呢
SQL> select count(distinct manager_id) peo from employees;
PEO
----------
18
真的经理也就18人
SQL> select max(salary)-min(salary) from employees;
MAX(SALARY)-MIN(SALARY)
-----------------------
21900
这很容易的
我们要经理号
经理付给雇员的最低薪水，那得按照经理号分组
经理idnull的，不要了，where搞定
还要having，过滤
最后排序降序，order by
逻辑屡清楚，然后写语句就好说了
SQL> select e.manager_id,min(e.salary) from employees e where e.manager_id is not null group by e.manager_id having min(e.salary) > 6000 order by min(e.salary);
MANAGER_ID MIN(E.SALARY)
---------- -------------
148 6100
147 6200
149 6200
108 6900
146 7000
145 7000
205 8300
102 9000
8 rows selected
自己写
首先要显示那列
同时你必须取的是经理非空的数据
然后分组是经理id分组
还得having过滤，然后排序
SQL> select e.manager_id,min(e.salary) from employees e where e.manager_id is not null group by e.manager_id having min(e.salary) > 6000 order by min(e.salary) desc;
MANAGER_ID MIN(E.SALARY)
---------- -------------
102 9000
205 8300
145 7000
146 7000
108 6900
147 6200
149 6200
148 6100
8 rows selected
降序哦
employees表
departments表
多表查询联合来玩——同步部门id来链接表
这个地点不是城市：？？？是id就行了
名字和地点——俩同时都要分组的
人数，部门内平均薪水的话——组合函数搞
SQL> select e.department_id,d.location_id,count(*),avg(salary) from employees e, departments d where e.department_id = d.department_id group by e.department_id,d.location_id;
DEPARTMENT_ID LOCATION_ID COUNT(*) AVG(SALARY)
------------- ----------- ---------- -----------
30 1700 6 4150
20 1800 2 9500
40 2400 1 6500
90 1700 3 19333.33333
110 1700 2 10154
70 2700 1 10000
80 2500 34 8955.882352
60 1400 5 5760
10 1700 1 4400
50 1500 45 3475.555555
100 1700 6 8601.333333
11 rows selected
这题要高清，到底分组的列是谁？
然后取他们的组合好说
两个列的分组要同时放
这显然是按照受雇日期来分组
过滤条件是要这几年的
然后查他们的人数总数
四个case，那decode吧
SQL> select count(*) total, sum(decode(to_char(hire_date,'yyyy'),'2001',1,0)) "2001", sum(decode(to_char(hire_date,'yyyy'),'2004',1,0)) "2004", sum(decode(to_char(hire_date,'yyyy'),'2006',1,0)) "2006", sum(decode(to_char(hire_date,'yyyy'),'2008',1,0)) "2008" from employees e;
TOTAL 2001 2004 2006 2008
---------- ---------- ---------- ---------- ----------
107 1 10 24 11
这题老复杂了
但是情况就是这样
这sql语句就很过分
对某一个列特定值，可以用decode来返回结果
挺狠的
这里就说是俩需求……
根据岗位分组，
计算薪水合计
SQL> select sum(e.salary) from employees e group by job_id;
SUM(E.SALARY)
-------------
28800
12008
8300
36400
11000
4400
34000
64300
39600
12008
13900
61000
13000
10000
24000
250500
6000
55700
6500
19 rows selected
这个很好说
根据几个部门id不同的情况
根据部门的
SQL> select sum(e.salary),sum(decode(department_id,20,salary,0)) "20",sum(decode(department_id,50,salary,0)) "50",sum(decode(department_id,80,salary,0)) "80",sum(decode(department_id,90,salary,0)) "90" from employees e group by job_id;
SUM(E.SALARY) 20 50 80 90
------------- ---------- ---------- ---------- ----------
28800 0 0 0 0
12008 0 0 0 0
8300 0 0 0 0
36400 0 36400 0 0
11000 0 0 0 0
4400 0 0 0 0
34000 0 0 0 34000
64300 0 64300 0 0
39600 0 0 0 0
12008 0 0 0 0
13900 0 0 0 0
61000 0 0 61000 0
13000 13000 0 0 0
10000 0 0 0 0
24000 0 0 0 24000
250500 0 0 243500 0
6000 6000 0 0 0
55700 0 55700 0 0
6500 0 0 0 0
19 rows selected
联合起来，总得还是工作岗位分组
细分岗位列不同，又再去搞
decode很机智
总结
提示：重要经验：
1）
2）学好oracle，即使经济寒冬，整个测开offer绝对不是问题！同时也是你考公网络警察的必经之路。
3）笔试求AC，可以不考虑空间复杂度，但是面试既要考虑时间复杂度最优，也要考虑空间复杂度最优。
754
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
