Oracle基础知识5----高级查询（分组查询、多表连接、子查询、案例）

测试数据来源：http://blog.csdn.net/ochangwen/article/details/51297893

还要准备一张表：工资级别表 losal:最低级别，hisal *别

一、分组查询

    分组函数(也叫聚合函数、多行函数，组函数)作用于一组数据，并对一组数据返回一个值。聚合函数忽略空值。
    常用分组函数avg,sum,min, max, count, wm_concat(拼接函数) ,variance(标准差),stddev(方差)

SQL> select  deptno 部门号, wm_concat(ename) 部门中员工的笱 from emp group by deptno;
        部门号 部门中员工的笱
----------- --------------------------------------------------------------------------------
          1 TOM,Steve
          2 曹仁人,酱油哥,曹操操
          3 刘备备,张飞飞,SCOTT
          6 SMITH
          8 宋祖英

//返回总记录数   //* 代表的是：一条记录SQL> select count(*) from emp;//返回comm不为空的总记录数SQL> select count(comm) from emp;//COUNT(DISTINCT expr) 返回 expr非空且不重复的记录总数SQL> select count(distinct(sal)) from emp;注意：组函数忽略空值。//返回所有员工的平均工资   SQL> select avg(nvl(sal,0)) from emp;注意：NVL函数使分组函数无法忽略空值//求该月本公司发出的工资总额SQL> select sum(comm)+sum(sal) from emp;SQL> select sum(nvl(sal,0)+nvl(comm,0)) from emp;

注意：select a,b,c ,组函数(x)
          from emp
          group by a,b,c;
    select里有多少字段，group by后面就有多少字段，但可以多于select里的字段。反之则不一定
注意：group by子句一定要与分组函数结合使用,否则没有意义。

-- 1、所用包含于SELECT 列表中，而未包含于组函数中的列都必须包含于 GROUP BY 子句中。
--举例：
SQL> select empno,count(job) from emp;--执行会有异常
-- 正确写法如下：
SQL> select empno,count(job) from emp group by empno;

-- 2、不能在 WHERE 子句中使用组函数（注意）。
SQL> select deptno from emp where count(job)>0 group by deptno;
备注：ORA-00934: 此处不允许使用分组函数

where与having的区别
  where子句中不能使用分组函数
  如果子句中没有分组函数，两者都可以用
注意：从SQL优化的角度上看，尽量使用where效率会高
  having 是先分组再过滤，而where相反

--查询部门3 的平均工资
select deptno, avg(sal) from emp group by deptno having deptno=3;
-- 或者
select deptno, avg(sal) from emp  where deptno=3 group by deptno ;

语法：
    SELECT    column, group_function    
    FROM    table
   [ WHERE    condition]
   [GROUP BY    group_by_expression]
   [ HAVING    group_condition]

   [ORDER BY    column];

order by 中列，应该出现在group by 子句中。这是一个很显然的道理。

SELECT dep.department_name, MAX(emp.salary), MIN(emp.salary) 
FROM departments dep 
LEFT JOIN employees emp 
ON (dep.department_id = emp.department_id) 
GROUP BY dep.department_name 
--order by 的列，必须是出现在group by 子句里的列  
ORDER BY dep.department_name DESC

二、多表连接

2-1.连接的类型

1).等值连接
  连接中有“=”。

-- 示例：查询员工信息，要求显示：员工号，姓名，月薪，部门名称
select e.deptno,e.ename,e.sal,d.dname 
from emp e, dept d where e.deptno=d.deptno;

2).不等值连接

-- 示例：查询员工信息，要求显示：员工号，姓名，月薪，薪水级别
select e.deptno,e.ename,e.sal,s.grade 
from emp e, salgrade s where e.sal between s.losal and s.hisal;

3).外连接
  核心：通过外连接， 把对于连接条件不成立的记录，仍然包含在最后的结果中 。
  左外链接：当连接条件不成立的时候，等号左边的表仍然被包含。
  右外链接：当连接条件不成立的时候，等号右边的表仍然被包含。

-- 示例：按部门统计员工人数，要求显示：部门号，部门名称，人数
select d.deptno,d.dname ,count(e.empno)
from emp e, dept d where e.deptno=d.deptno group by d.deptno,d.dname;
-- 上面的查询如果部门的人数为0就没有结果显示

--右外链接
select d.deptno,d.dname ,count(e.empno)
from emp e, dept d where e.deptno (+)=d.deptno group by d.deptno,d.dname;

-- 如果是左外链这么写：e.deptno =d.deptno(+)

4).自连接
  核心：通过别名，将同一张表视为多张表

-- 示例：查询员工姓名和员工的老板姓名
select e1.ename, e1.mgr
from emp e1, emp e2 
where e1.mgr=e2.ename;

  不适合操作大表，原因获取笛卡尔集是平方数据太大
  解决办法：层次查询
5).层次查询
  某些情况下，可以代替自连接
  本质情况下，是一个单表查询

三、子查询

3-1.子查询排序问题

   rownum 行号，伪列
  行号需要注意的两个问题
    1.行号永远按照默认的顺序
    2.行号只能使用<, <=; 不能使用> ,>=

-- 找到员工表中工资最高的前三名
select rownum,empno, sal 
from emp 
where rownum<=3
order by sal;
    ROWNUM       EMPNO         SAL
---------- ----------- -----------
         1           2        2000
         3           4        3000
         2           3        9000

select rownum,empno, sal 
from (select * from emp order by sal desc) 
where rownum<=3;
    ROWNUM       EMPNO         SAL
---------- ----------- -----------
         1           5       80000
         2           3        9000
         3           4        3000

-- 查询工资比10号部门任意一个员工高的员工信息，

-- >是单行操作符，
select * from emp 
where sal > any(select sal from emp where deptno=3);  
--等同于大于最小值就行
select * from emp 
where sal > (select min(sal) from emp where deptno=3); 

四、案例

案例1：

-- mysql用的是limit
 select r,empno,ename,sal
from (select rownum r,empno,ename,sal
      from (select rownum,empno,ename,sal 
            from (select * from emp order by sal desc)
                  where rownum <=8) e1 ) e2
where r>=5

案例2：