> 来源：[恒生LIGHT云社区](https://www.herecours.com/d/file/efpub/2026/03-03/20260303160409842955](https://www.herecours.com/d/file/efpub/2026/03-03/20260303160409842956.jpg "3384") nvl函数可以防止count自动忽略空值，它的作用是当comm为空时返回0，因为0是非空，所以会进入统计总数。  ### **group by子查询** 在select 列表中所有未包含在聚合函数中的列都应该包含在group by子句中。 ### **单列分组** 求每个部门的平均工资，显示部门号，部门的平均工资。 ``` select deptno,avg(sal) from emp group by deptno order by deptno ``` ### **多列分组** 按部门，不同职位，统计员工的工资总和 ``` select detpno,job,sum(sal) from emp group by deptno,job order by deptno ``` ### **过滤分组** having子句的使用 where与having的区别 * where子句中不能使用聚合函数，先过滤后分组 * having子句中可以使用聚合函数，先分组后过滤 ****注意：从SQL优化的角度上看，尽量使用where，因为where使得分组记录数大大降低，从而提高效率。**** 求平均工资大于2000的部门 ``` select deptno,avg(sal) from emp where(avg(sal)) > 2000 group by deptno ```  where子句中不能使用聚合函数，所以报错，改成having xxx子句即可。 ``` select deptno,avg(sal) from emp group by deptno having(avg(sal)) > 2000 ``` ### **在分组查询中使用order by** 求每个部门的平均工资，显示部门号，部门的平均工资，按照工资升序排列。 ``` select deptno,avg(sal) from emp group by deptno order by avg(sal) ``` 也可以按列的别名排序 ``` select deptno,avg(sal) avgsal from emp group by deptno order by avgsal ``` 还可以按列的序号排序，平均工资是第2列 ``` select deptno,avg(sal) from emp group by deptno order by 2 ``` 降序排列加上desc即可 ``` select deptno,avg(sal) from emp group by deptno order by 2 desc ``` ### **分组函数的嵌套** 求部门平均工资的最大值 ``` select max(avg(sal)) from emp group by deptno ``` ### **group by语句增强** 主要用在group by语句报表功能 每个部门，安装不同职位，求工资总和，部门小结，总结。  可以使用rollup函数 ``` select deptno,job,sum(sal) from emp group by rollup(deptno,job) ```  再设置一下显示格式，break on deptno表示相同的部门号只显示一个，skip 1表示不同的部门号之间空1行。  ### **完善报表显示** 增加标题，页码等 ttitle col 15 '我的报表' col 35 sql.pno 设置标题，空15列显示我的报表，然后空35列显示页码 col deptno heading 部门号 col job heading 职位 col sum(sal) heading 工资总额 以上3行设置列标题 break on deptno skip 1 设置显示格式，相同的部门号只显示一个，不同部门号之间空1行 将这些设置保存到一个sql文件（注意要改成ANSI编码，否则会出现乱码并且设置无效），然后通过get命令读取执行。再次执行查询语句，得到如下报表。如果出现了多页，为了显示美观，可以设置一页显示更多的行，比如设置每页显示100行：set pagesize 100  ## **多表查询** 上面的例子都是从单个表中查询数据，下面开始讲解从多个表中查询数据。 为了避免笛卡尔集，可以在where加入有效的连接条件，在实际允许环境下，应避免使用笛卡尔全集。  ### **等值连接** 实例：查询员工信息，要求显示：员工号，姓名，月薪，部门名称 需要查询员工表和部门表，通过部门号进行等值连接查询，where xxx=xxx ``` select e.empno,e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno ``` ### **不等值连接** 示例：查询员工信息，要求显示：员工号，姓名，月薪，薪水级别 需要查询员工表和薪水等级表，通过薪水等级上下限进行不等值连接查询。where xxx between xxx and xxx，注意：小值在between前面，大值在between后面 ``` select e.empno,e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal ``` ### **外连接** 示例：按部门统计员工人数，要求显示：部门号，部门名称，人数 需要查询部门表和员工表 以下是通过等值连接的方式查询，虽然总人数没有问题，但是少了一个部门，因为一个部门没有员工。 ``` select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数 from emp e,dept d where e.deptno=d.deptno group by d.deptno,d.dname ```  外连接一般通过join来实现，一张图看懂SQL的各种join用法。  使用join语句重新实现示例功能 ``` select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数 from dept d left join emp e on d.deptno=e.deptno group by d.deptno,d.dname ``` ### **自连接** 示例：查询员工姓名和员工的老板姓名 核心：通过别名，将同一张表视为多张表 ``` select e.ename 员工姓名,b.ename 老板姓名 from emp e, emp b where e.mgr=b.empno ``` 这种方式会产生笛卡尔集，不适合大表的查询，可以使用层次查询来解决。connect by xxx start with xxx level是层次查询提供的伪列，需要显示使用才会查询这个伪列。 ``` select level,empno,ename,sal,mgr from emp connect by prior empno=mgr start with mgr is null order by 1 ``` ## **子查询** ### **子查询语法中的小括号** 必须要有小括号，书写风格要清晰如下图所示： 示例：查询比FORD工资高的员工 ``` select * from emp where sal > (select sal from emp where ename='FORD') ``` ### **可以使用子查询的位置** select，from，where，having select位置的子查询只能是单行子查询，也就是只能返回一条结果 ``` select empno,ename,sal,(select job from emp where empno='7839') job from emp ``` #### **having位置的子查询** 示例：查找部门平均工资大于30号部门最大工资的部门号及其平均工资 ``` select deptno,avg(sal) from emp group by deptno having avg(sal) > (select max(sal) from emp where deptno=30) ``` **from位置的子查询** 查询结果也可以当成表 ``` select * from (select empno,ename,sal from emp) ``` 增加1列年薪，使用sal*12得到年薪 ``` select * from (select empno,ename,sal,sal*12 annsal from emp) ``` ### **主查询和子查询可以不是同一张表** 示例：查询部门名称是SALES的员工信息 使用子查询的方式： ``` select * from emp where deptno=(select deptno from dept where dname='SALES') ``` 使用多表查询的方式: ``` select e.* from emp e, dept d where e.deptno=d.deptno and d.dname='SALES' ``` ### **子查询的排序** 一般不在子查询中，使用排序；但在Top-N分析问题中，必须对子查询排序 示例：找到员工表中工资最高的前三名，如下格式：  rownum，行号，oracle自动为表分配的伪列。 * 行号永远按照默认的顺序生成 * 行号只能使用<,<=；不能使用>,>= ``` select rownum,empno,ename,sal from (select * from emp order by sal desc) where rownum<=3 ``` ### **子查询执行顺序** 一般先执行子查询，再执行主查询；单相关子查询例外。 相关子查询示例：找到员工表中薪水大于本部门平均薪水的员工 ``` select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal from emp e where sal > (select avg(sal) from emp where deptno=e.deptno) ``` ### **单行子查询和多行子查询** 单行子查询返回一个结果，只能使用单行操作符； 多行子查询返回多个结果，只能使用多行操作符。 #### **单行操作符：** | 操作符 | 含义 | | ------ | -------- | | = | 等于 | | > | 大于 | | >= | 大于等于 | | < | 小于 | | <= | 小于等于 | | <> | 不等于 | #### **多行操作符：** | 操作符 | 含义 | | ------ | ---------------------------- | | in | 等于列表中的任何一个 | | any | 和子查询返回的任意一个值比较 | | all | 和子查询返回的左右值比较 | #### **单行子查询示例1：** 查询员工信息，要求： 职位与7566员工一样，薪水大于7782员工的薪水 ``` select * from emp where job=(select job from emp where empno=7566) and sal >（select sal from emp where empno=7782） ``` #### **单行子查询示例2：** 查询最低工资大于20号部门最低工资的部门号和部门的最低工资 ``` select deptno,min(sal) from emp group by deptno having min(sal) > (select min(sal) from emp where deptno=20) ``` #### **多行子查询示例：** 查询部门名称是SALES和ACCOUNTING的员工信息 使用多行子查询的方式： ``` select * from emp where deptno in (select deptno from dept where dname='SALES' or dname='ACCOUNTING') ``` 使用多表查询的方式： ``` select e.* from emp e, dept d where e.deptno=d.deptno and (d.dname='SALES' or d.dname='ACCOUNTING') ``` ### **子查询中的空值问题** 查询不是老板的员工 注意：当子查询中包含null值时，不要使用not in。 a not in (10,20,null) a != 10 and a != 20 and a != null, a != null 永远不成立，所以整个表达式永远返回false。 可以在子查询中把null值过滤掉再使用not in。 ``` select * from emp where empno not in (select mgr from emp where mgr is not null) ```