在之前使用过count（*）函数，此函数的功能是可以取得一张表之中的全部数据量，而这种函数在数据库之中就将定义为统计函数，有些地方也将其称为分组函数 统计函数给出的8个统计函数，从sql的标准规定来讲，实际上只有5个是标准函数：count（）、sum（）、avg（）、max（）、min（）。 例题： 查找出公司每个月支出的月工资的总和 SELECT SUM(sal) FROM emp ; 查询出公司的最高工资、最低工资、平均工资 SELECT round(AVG(sal),2), MAX(sal), MIN(sal) FROM emp ; 统计出公司最早雇佣和最晚雇佣的雇佣日期 针对日期列肯定是hiredate字段，但是在oracle中的函数是可以进行数据类型的互相转换的，最早雇佣的日期一定是最小的，最晚日期一定是最大的 SELECT MIN(hiredate) 最早雇佣日期 , MAX(hiredate) 最晚雇佣日期 FROM emp ; 统计公司中间的工资值 例如：2000、3000、5000，中间值就是3000 SELECT MEDIAN(sal) FROM emp ; 统计工资的标准差与方差 SELECT STDDEV(sal) 标准差,VARIANCE(sal) 方差 FROM emp ; Count 对于count（）函数而言，实际上最早所使用的函数，但是针对于之前（）函数之中的参数可以使用*也可以使用字段 统计出公司的雇员人数 SELECT COUNT(empno) , COUNT(*) FROM emp ; 对于count（）函数而言，可以传递三类内容：*，字段、distinct 字段。 面试题：请问count（*）、count（字段）、count（distinct 字段）有什么区别？ SELECT COUNT(*) , COUNT(job) , COUNT(comm) , COUNT(DISTINCT job) FROM emp ; 在使用count（字段）是时候，如果列上存在了null，那么null是不进行统计的，如果用的是distinct那么列上如果有重复，重复的记录也不进行统计，使用count（*）是最方便的，不过我个人的建议，count（字段），使用一个不可能为null的列进行统计，例如：empno。 在所有的统计函数，只有count（）函数可以在表中没有任何数据的时候依然返回内容 Group by 分组 什么时候需要分组？ 需求一：在一个班级之中，要求男女个一组进行辩论赛。 需求二：在公司中饭，要求每个部门一组进行比赛。 对于以上的两个需求，假设存在学生表，那么在学生表中一定存在一个性别的字段，而每一部分的数据，其性别的内容一定是相同 而在公司之中，如果要进行部门的分组，肯定是需要有一个部门的列的内容存在重复。 可以查看emp表的全部数据，以确定是否可有分组的字段 发现此时的查询结果之中，job和deptno两个字段上都存在了重复的内容，虽然大部分情况下都允许在重复数据的列上实现分组，但是也允许一条数据一组，意义不大。 分组统计语法 例题： 统计出每个部门的人数 此时一定需要按照部门的信息分组，在emp表中，每个雇员的部门都使用一个部门的编号表示，那么就可以针对于deptno字段来实现分组。 select deptno,count(*) from emp group by deptno 例题： 统计出每种职位的最低和最高工资 SELECT job , MIN(sal) , MAX(sal) FROM emp GROUP BY job ; 以上操作并不复杂，可是在分组中，最为麻烦的地方就在于分组操作的若干个限制，很多同学学到此处不记，就会很麻烦。 事项二:在以后进行分组操作的时候，本着一个原则：group by 子句之中允许出现的字段才是在select子句中允许出现的字段。 事项三： 例题： 求出每个部门的平均工资最高的工资 SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno ; 例题： 查询每个部门的名称、部门人数、部门平均工资、平均服务年限 平均服务年限需要计算出年的概念，使用months_between()函数，但是在这个查询里面一定不能是一个表 确定所需要的数据表： Dept表：部门名称 Emp：部门人数、平均工资、平均服务年限，这些需要通过统计函数据算 关联字段： 雇佣于部门：emp.deptno=dept.deptno 正常的查询 SELECT d.dname,d.loc,e.empno,e.ename FROM emp e,dept d WHERE e.deptno=d.deptno ; SELECT d.dname, COUNT(e.empno) , ROUND(AVG(e.sal),2) avgsal, ROUND(AVG(MONTHS_BETWEEN(SYSDATE,e.hiredate) / 12),2) avgyear FROM dept d,emp e WHERE e.deptno(+)=d.deptno GROUP BY d.dname; 学习到此处就应该清楚认识到group by子句的使用，那么既然有group by也就有了新的子句顺序：from、where、group by、select、order by。 例题： 查询出公司各个工资等级,雇员的数量、平均工资。 SELECT s.grade,COUNT(e.empno), ROUND(AVG(e.sal),2) FROM emp e,salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal GROUP BY s.grade ; 确定需要的数据表： Salgrade表：工资等级信息 Emp表：数据的数量、平均工资的统计结果 确定已知的关联字段： 雇佣与工资等级：emp.sal between s.losal and s.hisal 以上的每个例题是针对于多表查询后的数据进行统计，其是根据返回结果进行分组，可以说是针对于临时表分组 例题： 统计出有提成与没有提成的雇员的平均工资、平均雇佣年限、雇员人数。 SELECT '领取佣金', ROUND(AVG(sal),2) avgsal, ROUND(AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12),2) avgyear, COUNT(empno) count FROM emp Select comm,round(avg(sal)),round(avg(months_between(sysdate,hiredate)/12),2),count(*) from emp group by comm 并没有所谓的实现提成分组，而是按照每一种可能出现的提成数来进行分组操作，下面需要更换思路：首先查询出有提成的雇员信息。 SELECT '有提成', ROUND(AVG(sal),2) avgsal, ROUND(AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12),2) avgyear, COUNT(empno) count FROM emp WHERE comm >0 ; SELECT '没提成', ROUND(AVG(sal),2) avgsal, ROUND(AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12),2) avgyear, COUNT(empno) count FROM emp WHERE comm IS NULL or comm=0 SELECT '没提成' 提成, ROUND(AVG(sal),2) avgsal, ROUND(AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12),2) avgyear, COUNT(empno) count FROM emp WHERE comm IS NULL or comm=0 UNION all SELECT '有提成', ROUND(AVG(sal),2) avgsal, ROUND(AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12),2) avgyear, COUNT(empno) count FROM emp WHERE comm>0 ; 拿到题目之后一定要分析一下，不要冲动直接就按照习惯的方式进行分组操作 多字段分组 在进行单字段分组的时候不管是单表取数据，还是多表查询，那么在一个列上一定存在重复记录。下面讲解的多字段分组，本质上没有区别，也就是说若干个列上的数据同时存在重复。 多字段分组统计 既然可以在group by 子句之中出现多个分组字段，那么在select子句里面也一定可以定义多个分组字段查询显示。 例题： 现在要求查询出每个部门的详细信息。（包含部门编号、部门名称、位置、平均工资、总工资、最高工资、最低工资、部门人数） 确定需要的数据表： Dept表：编号、名称、位置 Emp表：平均工资、总工资、最高工资、最低工资、人数 确定关联字段：
-
deptno=d.deptno
分析：先查询出多表关联 SELECT d.deptno,d.dname,d.loc,e.empno,e.ename FROM emp e,dept d WHERE e.deptno=d.deptno ; 会发现有三个列都在重复，那么就具备了分组的条件 SELECT d.deptno,d.dname,d.loc, COUNT(e.empno) count,ROUND(AVG(sal),2) avg,SUM(sal) sum,MAX(sal) max,MIN(sal) min FROM emp e,dept d WHERE e.deptno=d.deptno GROUP BY d.deptno,d.dname,d.loc ; 发现显示结果不够明朗 SELECT d.deptno,d.dname,d.loc, NVL(COUNT(e.empno),0) count,NVL(ROUND(AVG(sal),2),0) avg, NVL(SUM(sal),0) sum,NVL(MAX(sal),0) max,NVL(MIN(sal),0) min FROM emp e,dept d WHERE e.deptno(+)=d.deptno GROUP BY d.deptno,d.dname,d.loc ; Having 子句 必要说明：having子句一定要与group by子句一起使用。 例题： 查询出所有平均工资大于2000的职位信息、平均工资、雇员人数 分析: 首先不关心平均工资是否大于多少，只按照职位分组统计 SELECT job, ROUND(AVG(sal),2) , COUNT(empno) FROM emp GROUP BY job 现在希望可以针对于分组后的数据过滤，如果现在使用where是不可能的（因为where在group by 子句之前，而且不允许使用统计函数）所以利用having SELECT job, ROUND(AVG(sal),2) , COUNT(empno) FROM emp GROUP BY job HAVING AVG(sal)>2000 ; *重点* 对于限制所有的子句的语法就完整了，执行顺序：from、where、group by、having、select、order by。 例题： 列出至少有四个员工的所有部门编号、部门名称，并统计出这些部门的平均工资、最低工资、最高工资 分析： 需要的数据表： dept：部门编号、名称 Emp：统计信息 确定关联字段： D.deptno=e.deptno SELECT d.deptno,d.dname,e.empno,e.sal FROM emp e,dept d WHERE e.deptno(+)=d.deptno ; SELECT d.deptno,d.dname,ROUND(AVG(e.sal)),MIN(e.sal),MAX(e.sal) FROM emp e,dept d WHERE e.deptno(+)=d.deptno GROUP BY d.deptno,d.dname,d.loc ; 因为having在select之前使用 所有不能使用别名了 SELECT d.deptno,d.dname,ROUND(AVG(e.sal),2),MIN(e.sal),MAX(e.sal) FROM emp e,dept d WHERE e.deptno(+)=d.deptno GROUP BY d.deptno,d.dname HAVING COUNT(e.empno)>3; 语句执行流程： 对having和where的区别描述： Where：是在分组之前使用（可以没有group by），不允许使用统计函数。 Having：是在分组之后使用（必须结合group by），允许使用统计函数。 综合例题： 显示非销售人员（SALESMAN）工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于$5000，输出结果按月工资的合计升序排列 SELECT job , SUM(sal) sum FROM emp WHERE job<>'SALESMAN' GROUP BY job HAVING SUM(sal)>5000 ORDER BY sum ASC ; 子查询 子查询就是指的在一个完整的查询语句之中嵌套若干个不同功能的小查询，从而一起完成复杂查询的一种编写形式 复杂查询=限定查询+多表查询+统计查询+子查询。也是在笔试部分出现较多的部分 例题： 查询公司之中工资最低的雇员的完整信息 SELECT MIN(sal) FROM emp ; SELECT * FROM emp WHERE sal=( SELECT MIN(sal) FROM emp) ; 子查询就是在一个完整的查询之中定义了若干个小的查询所形成的复杂查询。注意所有的子查询都需要（）来标记的 子查询返回结果： 子查询语法： 子查询的常见操作： 子查询返回单行单列数据 如果子查询返回的是单行单列的数据，那么子查询所得到的就是一个数据。 例题： 查询出基本工资比ALLEN低的全部雇员信息 SELECT sal FROM emp WHERE ename='ALLEN' ; SELECT * FROM emp WHERE sal< ( SELECT sal FROM emp WHERE ename='ALLEN') ; 例题： 查询基本工资高于公司平均薪金的全部雇员信息 SELECT AVG(sal) FROM emp ; SELECT * FROM emp WHERE sal>( SELECT AVG(sal) FROM emp) ; 例题： 查找出与ALLEN从事同一工作，并且基本工资高于雇员编号为7521的全部雇员信息 SELECT job FROM emp WHERE ename='ALLEN' SELECT sal FROM emp WHERE empno=7521 ; SELECT * FROM emp WHERE job=( SELECT job FROM emp WHERE ename='ALLEN') AND sal>( SELECT sal FROM emp WHERE empno=7521) ; 子查询返回单行多列数据 例题： 查询与SCOTT从事同一工作且工资相同的雇员信息（不包括scott） SELECT job,sal FROM emp WHERE ename='SCOTT' ; SELECT * FROM emp WHERE (job,sal)=( SELECT job,sal FROM emp WHERE ename='SCOTT') AND ename<>'SCOTT' ; 习题： 查询与雇员7566从事同一工作且领导相同的全部雇员信息（不包括7566） SELECT * FROM emp WHERE ( job,mgr)=( SELECT job,mgr FROM emp WHERE empno=7566) AND empno<>7566 ; 例题： 查询与ALLEN从事同一工作且在同一年雇佣的全部雇员信息（包括ALLEN） SELECT job, TO_CHAR(hiredate, 'yyyy') FROM emp WHERE ename='ALLEN' ; SELECT * FROM emp WHERE (job, TO_CHAR(hiredate, 'yyyy'))=( SELECT job, TO_CHAR(hiredate, 'yyyy') FROM emp WHERE ename='ALLEN') ; 子查询返回多行单列数据 在使用多行子查询时，主要使用三种操作符：in、any、all In操作符： 在之前学习过的限定查询之中已经使用过了in操作符，主要是设置范围，也就是说如果子查询返回的是多行单列，就相当于定义了一个数据的查询范围。既然是范围的查询，就要使用in，也可以使用not in 操作。 例题： 查询出与每个部门中最低工资的全部雇员信息 SELECT MIN(sal) FROM emp GROUP BY deptno ; SELECT * FROM emp WHERE sal IN ( SELECT MIN(sal) FROM emp GROUP BY deptno) ; 例题： 查询出不与每个部门中最低工资相同的全部雇员信息 分析： 相当于求反，不在范围中 SELECT * FROM emp WHERE sal NOT IN ( SELECT MIN(sal) FROM emp GROUP BY deptno) ; 说到in和not in的操作，那么就有一点注意，关于null的问题，如果在in之中子查询返回的数据有null，那么不会影响，如果在not in之中子查询返回数据有null，那么就表示不会有任何的数据返回。 Select mgr from emp Select * from emp where empno not in(Select mgr from emp) Any 操作符 做一个提前准备： 首先查找出每个部门经理的最低工资 SELECT MIN(sal) FROM emp WHERE job='MANAGER' GROUP BY deptno ; 考虑一个部门可能有多个经理的情况 使用=ANY操作符完成查询 SELECT * FROM emp WHERE sal=ANY ( SELECT MIN(sal) FROM emp WHERE job='MANAGER' GROUP BY deptno) ; All操作符 使用<>ALL操作符完成查询 这个功能和not in 完成一样的，可是如果使用的是=all，是没有任何数据返回的。 例题： 查询入职日期比30部门入职日期最早的员工还要早的员工姓名，入职日期 总结： In操作符:in、not in Any操作符： =Any：功能于in相同， >Any：表示比子查询之中返回的最小值要大 <any: 表示比子查询之中返回的最大值要小 All操作符： <>all：功能于not in 相同 >all：比子查询之中返回的最大值要大 <all：比子查询之中返回的最小值要小 空数据判断 exists（） 在sql之中提供了一个exists结构用于判断子查询是否有数据返回。如果子查询中有数据返回，则exists结构返回true，反之返回false 验证exists结构 SELECT * FROM emp WHERE EXISTS( SELECT * FROM emp WHERE empno=9999) ; 此时由于不存在9999编号的雇员，所以在这个地方exists（）判断返回就是false就不会有内容返回 如果现在有结果返回，子查询有内容，而且不管多少内容都会返回 使用NOT EXISTS求反 总结： Where子句可以判断单个数值、多个数值 使用in、any、all可以处理多行单列子查询 利用exists（）可以判断查询结果是否为null 在having子句中使用子查询 Having一定是结合group by子句一起使用的，其主要目的是进行分组后数据的再次过滤，而且与where子句不同的是，having是在分组后，可以使用统计函数 一般而言在having子句之中出现子查询，子查询返回的数据往往是单行单列，它是按照一个数值的方式返回，在通过统计函数进行过滤。 例题： 查询部门编号、雇员人数、平均工资，并且要求这些部门的平均工资高于公司平均薪金 SELECT AVG(sal) FROM emp ; --公司的平均薪金 SELECT deptno, COUNT(empno), AVG(sal) FROM emp GROUP BY deptno ; SELECT deptno, COUNT(empno), AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal)>( SELECT AVG(sal) FROM emp); 例题： 查询出每个部门平均工资最高的部门名称及平均工资 SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno ; SELECT d.dname, ROUND(AVG(e.sal),2) FROM emp e ,dept d WHERE e.deptno=d.deptno GROUP BY d.dname HAVING AVG(sal)=( SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno) ; 总结： 在having子句中使用子查询，子查询返回的都是单行单列数据，同时也可以在having中利用统计函数进行判断 在from后面使用子查询 From子句的主要功能是确定数据来源，那么来源都属于数据表，表的特征：行+列的集合。只要是在from子句之中出现的内容一般都是多行多列的子查询返回。 例题： 要求查询出每个部门的编号、名称、位置、部门人数、平均工资 SELECT e.deptno ,d.dname,d.loc, COUNT(empno) count , ROUND(AVG(sal),2) avg FROM emp e,dept d Where e.deptno=d.deptno GROUP BY deptno ; 除了以上的做法之外，现在还可以有另一种做法，利用子查询完成。 SELECT d.deptno,d.dname,d.loc,temp.count,temp.avg FROM dept d, (SELECT deptno dno, COUNT(empno) count , ROUND(AVG(sal),2) avg FROM emp GROUP BY deptno) temp WHERE d.deptno=temp.dno(+) ; 出现一个问题：两种操作可以实现同样的结果，那么使用那种好呢？ 为了解决此问题，可以将数据扩大100倍，即：emp表中的数据为1400条记录，而dept表中的数据为400条记录。 分析一： 当dept和emp表关联的时候一定会存在笛卡尔积，数据量：emp表的1400条*dept表的400条=560000条记录。 分析二：子查询 统计：emp表的1400条记录，而且最终的统计结果的行数不可能超过400行记录（部门就是400个）。 多表关联：dept表的400条记录*子查询的400条记录=160000条记录. 最终结果：160000+1400 =161400条记录 使用子查询实际上是解决多表查询所带来的性能问题，所有在开发之中子查询一定会大量使用。 例题： 查询出所有在部门“SALES”（销售部）工作的员工的编号、姓名、基本工资、奖金、职位、雇佣日期、部门的最高和最低工资。 分析： 需要数据表： Dept表：销售部，最终是根据销售部的统计出部门编号 Emp表：编号、姓名、基本工资、奖金、职位、雇佣日期、部门 Emp表：统计最高和最低工资 SELECT deptno FROM dept WHERE dname='SALES' ; --销售部的编号 SELECT empno ,ename ,sal ,comm ,job ,hiredate FROM emp WHERE deptno=( SELECT deptno FROM dept WHERE dname='SALES') ; ---雇员信息出来了 统计出最高和最低工资，使用的一定max()和min() 两个函数，但是对于统计函数的使用限制。 统计函数要么单独使用，要么集合group by使用，单独使用的时候select子句之中无法出现任何的字段，结合group by使用的时候，select子句之中允许出现分组字段。 统计函数嵌套的时候不允许出现任何的字段，包括分组字段。 发现在整个select查询里面需要统计查询，但是却无法直接使用统计查询，那么就可以在子查询中完成，而且这个子查询一定返回多行多列的数据，在from子句中出现。 select t2.empno,t2.ename,t2.sal,t2.comm,t2.job,t2.hiredate,t3.mx,t3.mn from dept t1, emp t2, (select deptno,max(sal) mx,min(sal) mn from emp group by deptno)t3 where t1.deptno=t2.deptno and t1.deptno=t3.deptno and t1.dname='SALES' 例题： 查询出所有薪金高于公司平均薪金的员工编号、姓名、基本工资、职位、雇佣日期，所在部门名称、位置，上级领导姓名，公司的工资等级，部门人数、平均工资、平均服务年限。 分析： 需要数据表： Dept表：部门名称、位置 Emp表：员工编号、姓名、基本工资、职位、雇佣日期 Emp表：上级名称 Salgrade表:工资等级 Emp表：统计出部门人数、平均工资、平均服务年限 关联字段：
-
deptno=d.deptno
-
mgr=m.empno
e.sal between s.losal and s.=hisal
SELECT AVG(sal) FROM emp ; --平均工资
SELECT e.empno, e.ename, e.sal, e.job, e.hiredate
FROM emp e
WHERE e.sal>(
SELECT AVG(sal)
FROM emp) ;
------------------------
SELECT e.empno, e.ename, e.sal, e.job, e.hiredate, d.dname, d.loc
FROM emp e , dept d
WHERE e.sal>(
SELECT AVG(sal)
FROM emp)
AND e.deptno=d.deptno ;
------------------------
SELECT e.empno, e.ename, e.sal, e.job, e.hiredate, d.dname, d.loc, m.ename mname
FROM emp e , dept d , emp m
WHERE e.sal>(
SELECT AVG(sal)
FROM emp)
AND e.deptno=d.deptno
AND e.mgr=m.empno(+);
SELECT e.empno, e.ename, e.sal, e.job, e.hiredate, d.dname, d.loc, m.ename mname , s.grade ,
temp.count, temp.avg , temp.avgyear
FROM emp e , dept d , emp m ,salgrade s ,(
SELECT deptno dno, COUNT(empno) count ,
ROUND(AVG(sal),2) avg , ROUND(AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12),2) avgyear
FROM emp
GROUP BY deptno) temp
WHERE e.sal>(
SELECT AVG(sal)
FROM emp)
AND e.deptno=d.deptno
AND e.mgr=m.empno(+)
AND e.sal BETWEEN s.losal AND s.hisal
AND e.deptno=temp.dno;
自己表达：
select e.deptno,e.empno,e.ename,e.sal,e.job,e.hiredate,d.dname 部门名称,
d.loc 位置,m.ename 领导姓名,s.grade 工资等级,t.cc 人数,t.aa 平均工资,t.bb 平均服务年限
from emp e,dept d,emp m,salgrade s,
(select deptno,count(empno) cc,round(avg(sal)) aa,
round(avg(months_between(sysdate,hiredate)/12)) bb
from emp group by deptno ) t
where e.deptno=d.deptno
and e.mgr=m.empno(+)
and e.sal between s.losal and s.hisal
and e.deptno=t.deptno
and e.sal>(select avg(sal) from emp)
例题:
列出薪金比“ALLEN”或“CLARK”多的所有员工的编号、姓名、基本工资、部门名称、其领导姓名，部门人数。
需要表;
Emp表：员工的编号、姓名、基本工资
dept表：部门名称
Emp表：领导姓名
Emp表：部门人数统计
关联字段：
D.deptno=d.deptno
M.mgr=e.empno
SELECT sal FROM emp WHERE ename IN ('ALLEN' , 'CLARK') ;
SELECT e.empno, e.ename, e.sal
FROM emp e
WHERE e.sal >ANY(
SELECT sal
FROM emp
WHERE ename IN ('ALLEN' , 'CLARK'))
AND e.ename NOT IN('ALLEN','CLARK');
SELECT e.empno ,e.ename , e.sal , d.dname
FROM emp e , dept d
WHERE e.sal>ANY(
SELECT sal
FROM emp
WHERE ename IN('ALLEN','CLARK'))
AND e.ename NOT IN('ALLEN','CLARK')
AND e.deptno=d.deptno ;
SELECT e.empno ,e.ename , e.sal , d.dname,m.ename mname,temp.count
FROM emp e , dept d , emp m , (
SELECT deptno dno , COUNT(empno) count
FROM emp
GROUP BY deptno) temp
WHERE e.sal>ANY(
SELECT sal
FROM emp
WHERE ename IN('ALLEN','CLARK'))
AND e.ename NOT IN('ALLEN','CLARK')
AND e.deptno=d.deptno
AND e.mgr=m.empno(+)
AND e.deptno=temp.dno ;
select e.empno,e.ename,e.sal,d.dname,m.ename,t.xx ----自己理解的
from emp e,dept d,emp m,
(select deptno,count(*) xx from emp group by deptno) t
where e.deptno=d.deptno
and e.mgr=m.empno(+)
and e.deptno=t.deptno
and e.sal>any(select sal from emp where ename in ('ALLEN','CLARK'))
and e.ename not in ('ALLEN','CLARK');
例题：
列出公司各个部门的经理（假设每个部门只有一个经理，job为“MANAGER”）的姓名、薪金、部门名称、部门人数、部门平均工资。
SELECT ename, sal
FROM emp
WHERE job='MANAGER' ;
SELECT e.ename, e.sal, d.dname
FROM emp e, dept d
WHERE job='MANAGER'
AND e.deptno=d.deptno ;
SELECT e.ename, e.sal, d.dname , temp.count , temp.avg
FROM emp e, dept d , (
SELECT deptno dno , COUNT(empno) count , ROUND(AVG(sal),2) avg
FROM emp
GROUP BY deptno) temp
WHERE job='MANAGER'
AND e.deptno=d.deptno
AND e.deptno=temp.dno ;
总结：
From子句出现的子查询返回结果为多行多列
利用子查询可以解决多表查询所带来的性能问题
Select子句的子查询
子查询可以出现在任意的位置上，不过从实际的项目来讲，在where、from、having子句之中使用子查询的情况还是比较多的，而对于select子句，只能是以一种介绍的形式进行说明(标量子查询)。
例题：
查询出公司每个部门的编号、名称、位置、部门人数、平均工资
SELECT deptno , dname , loc ,
(SELECT COUNT(empno) FROM emp WHERE deptno=d.deptno) count ,
(SELECT AVG(sal) FROM emp WHERE deptno=d.deptno) avg
FROM dept d ;
这类的子查询完全可以通过其他的形式实现，所有意义就不是很大
With子句
使用with子句创建临时表
临时表实际上就是一个查询结果，如果一个查询结果返回的是多行多列，那么就可以将其定义在from子句之中，表示其为一张临时表。除了在from子句之中出现临时表之外，也可以利用with子句直接定义临时表，就可以绕开了from子句。
使用WITH子句将emp表中的数据定义为临时表
WITH e AS (
SELECT * FROM emp)
SELECT *
FROM e ;
例题：
查询每个部门的编号、名称、位置、部门平均工资、人数
WITH e AS (
SELECT deptno dno , ROUND(AVG(sal),2) avg , COUNT(*) count
FROM emp
GROUP BY deptno)
SELECT d.deptno,d.dname,d.loc,e.count,e.avg
FROM e , dept d
WHERE e.dno(+)=d.deptno ;
例题：
查询每个部门工资最高的雇员编号、姓名、职位、雇佣日期、最高工资、部门编号、部门名称，显示的结果按照部门编号进行排序
WITH e AS (
SELECT deptno dno , MAX(sal) max
FROM emp
GROUP BY deptno)
SELECT em.empno , em.ename , em.job , em.hiredate , e.max , d.deptno,d.dname
FROM e , emp em , dept d
WHERE e.dno=em.deptno AND em.sal=e.max AND e.dno=d.deptno
ORDER BY em.deptno ;
分析函数
本内容在开发中不一定100%用到，讲解它只是为了扩充大家的知识层次。
传统sql的问题：
传统sql就是sql标准规定的语法：select、from、where、group by、having、order by，但是传统sql所能够完成的功能实际上不多。
在分析函数之中也可以使用若干统计函数，count（）等进行操作。
分析函数的语法
组合顺序
使用PARTITION子句
Select deptno,ename,sal from emp
这个时候只是一个简单查询，但是这个select子句里面是不可能出现统计函数的，
例如：count（）、sum（），但是如果说现在有了分析函数语法的支持就能实现了
SELECT deptno , ename, sal
FROM emp ;
SELECT deptno , ename, sal ,
SUM(sal) OVER (PARTITION BY deptno) sum
FROM emp ;
现在的数据是按照部门进行统计的，而后每行数据之后都会统计出这个结果
不使用PARTITION进行分区，直接利用OVER子句操作
SELECT deptno , ename, sal ,
SUM(sal) OVER () sum
FROM emp ;
如果没有分区，那么会把所有的数据当成一个区，一起进行统计。
通过PARTITION设置多个分区字段
SELECT deptno , ename, sal , job ,
SUM(sal) OVER (PARTITION BY deptno , job) sum
FROM emp ;
观察ORDER BY子句
Order by子句的作用主要就是进行排序，但是现在实现的是分区内数据的排序，而这个排序会直接影响到最终的查询结果。
本次是按照部门编号分区，而后按照里面的工资进行降序排列
SELECT deptno , ename, sal ,
RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) rk
FROM emp ;
设置多个排序字段order by （sal和hiredate）
SELECT deptno , ename, sal , hiredate ,
RANK() OVER (PARTITION BY deptno ORDER BY sal , hiredate DESC) rk
FROM emp ;
直接利用ORDER BY排序所有数据
求薪金总和，按照名字排序
SELECT deptno , ename, sal , hiredate ,
SUM(sal) OVER (ORDER BY ename DESC) SUM
FROM emp ;
Order by子句选项
在order by 子句之中还存在两个选项：nulls first和nulls last。其中nulls first 表示在进行排序前，出现null值的数据行排列在最前面，而nulls last则表示出现null值数据行排列在最后面。
使用NULLS LAST
SELECT deptno , ename, sal , comm ,
RANK() OVER (ORDER BY comm DESC NULLS LAST) rk ,
SUM(sal) OVER (ORDER BY comm DESC NULLS LAST) SUM
FROM emp ;
数据统计函数
例题：
查询雇员编号是7369的雇员姓名、职位、基本工资、部门编号、部门的人数、平均工资、最高工资、最低工资、总工资
分析一：
统计出全部数据
SELECT *
FROM (
SELECT empno,ename,job,sal,deptno ,
COUNT(empno) OVER (PARTITION BY deptno) count ,
ROUND(AVG(sal) OVER (PARTITION BY deptno)) avg ,
SUM(sal) OVER (PARTITION BY deptno) sum ,
MAX(sal) OVER (PARTITION BY deptno) max ,
MIN(sal) OVER (PARTITION BY deptno) min
FROM emp ) temp
WHERE temp.empno=7369 ;
等级函数
dense_rank()
有重复的数字不跳着排列,比如，两个并列第一，后面是第二名。
rank()
有重复的数字跳着排列,比如，两上并列第一，后面是第三名。
row_number()
不管重复值，会依次按顺序排序。
等级函数的主要功能是为数据进行排序编号使用的操作。
观察RANK()和DENSE_RANK()函数
SELECT deptno,ename,sal,
RANK() OVER (PARTITION BY deptno ORDER BY sal desc) rank_result ,
DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal desc) dense_rank_result
FROM emp ;
在使用rank（）的时候如果有相同的，那么出现跳号，如果是dense_rank（）就继续保持序号。
Row_number（）函数
Row_number（）函数的功能就是用于生成一个行的记录号。
SELECT deptno,ename,sal,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal) row_result_deptno ,
ROW_NUMBER() OVER (ORDER BY sal) row_result_all
FROM emp ;
------------------------
row_number 分析：
select deptno,sal,
row_number() over( PARTITION BY deptno ORDER BY sal DESC) rn1,
row_number() over( ORDER BY sal DESC) rn2,
---row_number() over( PARTITION BY deptno ) rn3
row_number() over( PARTITION BY deptno ORDER BY deptno DESC) rn4
from emp
/* 说明：
1、row_number() over( PARTITION BY deptno ) rn3,
结果：必须加 order by
2、 PARTITION BY deptno 可省略
结果：省略后，相当于rownum
3、order by 后面跟的列 以及 (desc/asc)对查询后的结果有影响。
4、PARTITION BY 对结果有影响
5、 row_number() 括号里面不能加任何参数
*/
分析函数的语法结构一般是：
分析函数名(参数|列) OVER (PARTITION BY子句 ORDER BY子句 )。
即由以下三部分组成：
分析函数名：如sum、max、min、count、avg等聚集函数以及lead、lag行比较函数等；
over： 关键字，表示前面的函数是分析函数，不是普通的集合函数；
分析子句：over关键字后面挂号内的内容；
row_number() over(PARTITION BY col1 ORDER BY col2 DESC)
-- 表示根据col1分组，在分组内部根据col2排序
等级函数
--而此函数计算的值就表示每组内部排序后的顺序编号（组内是连续且唯一的）
rank() over(PARTITION BY col1 ORDER BY col2 DESC)
-- 表示根据col1分组，在分组内部根据col2排序，
-- 结果是不连续的，如果有4个人，其中有3个是并列第1名，那么最后的排序结果结果如：1 1 1 4
dense_rank() over(PARTITION BY col1 ORDER BY col2 DESC)
-- 表示根据col1分组，在分组内部根据col2排序，
-- 结果是连续的，如果有4个人，其中有3个是并列第1名，那么最后的排序结果结果如：1 1 1 2
行列转换
行列转换操作严格来讲是一种小技巧，为了说明问题，下面我们来通过例题来演示
查询每个部门中各个职位的总工资
SELECT deptno , job , SUM(sal)
FROM emp
GROUP BY deptno , job ;
不利于用户浏览
如果是用户的浏览，更多是希望可以按照部门中的职位进行显示。
如果按照原始的方式实现，那么只需要利用decode（）函数就可以了
SELECT deptno ,
SUM(DECODE(job, 'PRESIDENT' , sal , 0 )) PRESIDENT_JOB ,
SUM(DECODE(job, 'MANAGER' , sal , 0)) MANAGER_JOB ,
SUM(DECODE(job , 'ANALYST' , sal , 0 )) ANALYST_JOB ,
SUM(DECODE(job , 'CLERK' , sal, 0 )) CLERK_JOB ,
SUM(DECODE(job , 'SALESMAN' , sal , 0)) SALESMAN_JOB
FROM emp
GROUP BY deptno ;
以上的方式使用的是decode（）函数，但是对于这个函数属于oracle自己的特色，如果没有decode（）函数呢，一般的数据库怎么样操作呢？只能利用select子句使用子查询的方式完成。
SELECT temp.dno, SUM(president_job) , SUM(manager_job) , SUM(analyst_job) , SUM(clerk_job) , SUM(salesman_job)
FROM (
SELECT deptno dno ,
(SELECT SUM(sal) FROM emp WHERE job='PRESIDENT' AND empno=e.empno) PRESIDENT_JOB ,
(SELECT SUM(sal) FROM emp WHERE job='MANAGER' AND empno=e.empno) MANAGER_JOB ,
(SELECT SUM(sal) FROM emp WHERE job='ANALYST' AND empno=e.empno) ANALYST_JOB ,
(SELECT SUM(sal) FROM emp WHERE job='CLERK' AND empno=e.empno) CLERK_JOB ,
(SELECT SUM(sal) FROM emp WHERE job='SALESMAN' AND empno=e.empno) SALESMAN_JOB
FROM emp e ) temp
GROUP BY temp.dno
ORDER BY temp.dno DESC ;
虽然是实现了功能，但是从效果上看，感觉过于复杂了
习题：
有数据如下
date result
2011-02-01 胜
2011-02-01 负
2011-02-01 胜
2011-02-02 胜
2011-02-02 负
显示结果
hiredate 胜 负
2011-02-01 2 1
2011-02-02 1 1
Pivot和unpivot函数
在oracle 11g版本之后，专门增加了pivot和unpivot两个转换函数
Pivot函数 --行转列
行转列语法
select 列
from 表
pivot 被聚合的列
for 哪列进行行转列
in（行转列中的项可以取别名）
select deptno,job,sum(sal) from emp group by deptno,job --想行转换列
SELECT * FROM (SELECT deptno , job , sal FROM emp)
PIVOT (
SUM(sal)
FOR job IN (
'PRESIDENT' AS president_job ,
'MANAGER' AS manager_job ,
'ANALYST' AS analyst_job ,
'CLERK' AS clerk_job ,
'SALESMAN' AS salesman_job
)
) ORDER BY deptno ;
查询更多统计信息
SELECT * FROM (
SELECT job ,deptno , sal,
SUM(sal) OVER(PARTITION BY deptno) sum_sal ,
MAX(sal) OVER(PARTITION BY deptno) max_sal ,
MIN(sal) OVER(PARTITION BY deptno) min_sal
FROM emp)
PIVOT (
SUM(sal)
FOR job IN (
'PRESIDENT' AS president_job ,
'MANAGER' AS manager_job ,
'ANALYST' AS analyst_job ,
'CLERK' AS clerk_job ,
'SALESMAN' AS salesman_job
)
) ORDER BY deptno ;
例题：
测试数据 (id，类型名称，销售数量)
create table demo(id int,name varchar(20),nums int); ---- 创建表
insert into demo values(10, 'apple', 1000);
insert into demo values(20, 'apple', 2000);
insert into demo values(30, 'apple', 4000);
insert into demo values(20, 'orange', 5000);
insert into demo values(10, 'orange', 3000);
insert into demo values(30, 'grapes', 3500);
insert into demo values(10, 'banana', 4200);
insert into demo values(20, 'banana', 5500);
---------列转行
select * from(select id,name,nums from demo)
pivot(
sum(nums) for name in(
'apple' as 苹果,
'banana' as 香蕉，
'orange' as 橙子，
'grapes' as 葡萄
)
) order by id
Unpivot函数 --列转行
列转行语法
select 列 from表
unpivot (默认去掉空值，保留空值include nulls)
（不是聚合函数，普通的列(自己取名字的列)。对应的具体值的列（原每一行数据划归为一列数据）
for 对应转换后列的名称(原列名划归一列数据）列名自由起
in （具体列到行的名称））
验证UNPIVOT函数
WITH temp AS (
SELECT * FROM (SELECT deptno , job , sal FROM emp)
PIVOT (
SUM(sal)
FOR job IN (
'PRESIDENT' AS PRESIDENT_JOB ,
'MANAGER' AS MANAGER_JOB ,
'ANALYST' AS ANALYST_JOB ,
'CLERK' AS CLERK_JOB ,
'SALESMAN' AS SALESMAN_JOB
)
) ORDER BY deptno )
SELECT * FROM temp
UNPIVOT (
sal_sum FOR job IN (
president_job AS 'PRESIDENT' ,
manager_job AS 'MANAGER' ,
analyst_job AS 'ANALYST' ,
clerk_job AS 'CLERK' ,
salesman_job AS 'SALESMAN'
)
) ORDER BY deptno ;
用传统函数显示出来
----行转列
select deptno,sum(case when job='CLERK' THEN sal END) 业务员 ,
sum(case when job='MANAGER' THEN SAL END) 经理,
sum(CASE WHEN JOB='PRESIDENT' THEN SAL END) 总裁,
sum(CASE WHEN JOB='SALESMAN' THEN SAL END) 销售员,
sum(CASE WHEN JOB='ANALYST' THEN SAL END) 分析师
from EMP
group by deptno
-------列转行
select deptno,case when deptno=10 then job
when deptno=20 then job
when deptno=30 then job
end job,
case when job= 'CLERK' then sal
when job='SALESMAN' then sal
when job='MANAGER' then sal
when job='PRESIDENT'then sal
when job='ANALYST' then sal
end sal
from emp
wm_concat函数
首先让我们来看看这个神奇的函数wm_concat(列名),该函数可以把列值以","号分隔起来,并显示成一行,接下来上例子,看看这个神奇的函数如何应用准备测试数据
create table test(id number,name varchar2(20));
insert into test values(1,'a');
insert into test values(1,'b');
insert into test values(1,'c');
insert into test values(2,'d');
insert into test values(2,'e');
显示结果如下：
select wm_concat(name) name from test;
按ID分组合并name
select id,wm_concat(name) name from test group by id