oracle的分组查询和连接查询

分组函数:

六个常用的分组函数:

AVG,SUM,MIN,MAX,COUNT,WM_CONCAT: 行转列

PS:分组函数默认会自动过滤控制,可以使用NVL函数使分组函数无法忽略空值:

未使用NVL函数: select sum(comm)/count(*) 一,sum(comm)/count(comm) 二,avg(comm) 三 from emp; --若有空记录，第一个结果和二三结果的不同

使用NVL函数: select count(*) 一, count(nvl(comm,0)) 二 from emp;

 

分组函数的语法格式 :

PS:未包含在分组函数的列，必须要在group by子句后面

select a,b,c ，分组函数(d) from emp group by a,b,c;

 

如何过滤分组数据: having 

PS: having与where的区别： where子句中不能使用分组函数,having必须和group by联用

select deptno,avg(sal) from emp group by deptno having avg(sal) >4000

 

order by

select deptno,avg(sal) from emp group by deptno order by avg(sal)

 

分组函数的嵌套：

select   max(avg(sal)) from emp group by  deptno;

 

group by增强:

PS: group by rollup(a,b) 等价于  group by a,b +group by a, group by null

select deptno,job,sum(sal) from emp group by rollup(deptno,job);

 

查询结果如下：

10    CLERK        2689.2
10    MANAGER        4807.96
10    PRESIDENT    6084.64
10                13581.8
20    CLERK        5456.11
20    ANALYST        6736.6
20    MANAGER        5093.17
20                17285.88
30    CLERK        2728.04
30    MANAGER        5025.26
30    SALESMAN    11261.79
30                19015.09
                49882.77

 

多表查询：

笛卡尔积的概念：笛卡尔积就是列数相加，行数相乘

例如两张表emp,dept的数据如下是：

 

 

笛卡尔积的数据的sql如下

select * from emp,dept

结果如下：共56行数据

 

连接类型：

　　等值连接: e.deptno = d.deptno
　　不等值连接 : from emp e, salgrade s where e.sal > s.losal and e.sal < s.hisal
　　外链接 : 通过外链接，把对于连接条件不成立的记录，任然包含在最后的结果中
　　　　左外链接：=(+)
　　　　右外连接： (+)=     : 下方的例子若不用右连接则查不出来两边表中不存在的deptno的记录

右连接的作用: select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数
            from emp e, dept d
            where e.deptno (+)=d.deptno
            group by d.deptno,d.dname;

　　　　查询结果如下：

　　　　若是不用右连接，sql语句中少了"(+)"，则查询结果如下：

 

　　自连接:通过别名，将同一张表视为多张表

select e.ename 员工姓名, b.ename 老板姓名
                from emp e ,emp b
                where e.mgr = b.empno

　　　　查询结果如下：

　　　　自连接缺点：不适合操作大表，一张表做一次自连接 笛卡尔积会是记录的平方；做两次自连接笛卡尔积是记录的立方

　　　　解决方法：层次查询　　　

--上一层的员工号(prior empno)是当前层的老板号(mgr) 

--level 伪列字段：表中不存在的字段 代表当前记录的的层级

select level,empno,ename,sal,mgr
from emp 
connect by prior empno = mgr
start with empno = 7839
order by 1;    

　　　　查询结果如下：

　　　　　　1)与自连接优点：是单表查询，不会产生笛卡尔积
　　　　　　2)与自连接缺点：结果不直观

　　　　自连接与层级查询对比的优点：结果比较直观