Oracle 表操作-组函数

/*
以下语句均 基于 [scott/tiger] 用户下的操作
*/
--分组统计各部门下工资>500的员工的平均工资
Select Avg(Sal) From Emp Where Sal>500 Group By Deptno ; 

--平均工资，处理null情况
Select Round(Avg(Nvl(Comm, 0))) From Emp;

--having 语句
Select Deptno, Job, Avg(Sal) 
From Emp 
Where Hiredate >= To_Date('1981-05-01','yyyy-mm-dd') 
Group By Deptno,Job Having Avg(Sal) > 1200 
Order By Deptno,Job;

--统计各部门下平均工资大于500的部门 
Select Deptno,Avg(Sal) From Emp 
Group By Deptno Having Avg(Sal)>500 ;

--算出部门30中得到最多奖金的员工奖金 
Select Max(Comm) From Emp Where Deptno = 30 ; 

--算出部门30中得到最多奖金的员工姓名 
Select Ename,Comm From Emp
Where Comm = (
    Select Max(Comm)
    From Emp Where deptno=30);
    
--算出每个职位的员工数和最低工资 
Select  Job,Min(Sal),Count(*) Num From Emp Group By Job;

--case查询
Select Empno, Ename, Sal, 
Case Deptno  
When 10 Then '财务部' 
When 20 Then '研发部' 
When 30 Then '销售部' 
Else '未知部门' 
End  部门 
From Emp; 

--group by 和 order by的联合使用
Select Sum(Sal+Nvl(Comm,0)) 
From Emp 
Group By Emp.Job
Order By Sum(Sal+Nvl(Comm,0)) Desc;

Select Job,Deptno,Sum(Sal+Nvl(Comm,0)) 
From Emp 
Group By Job,Deptno
Order By Sum(Sal+Nvl(Comm,0)) Desc;

Select Deptno,Job,Sum(Sal+Nvl(Comm,0)) 
From Emp 
Group By Deptno,Job;
Order By Sum(Sal+Nvl(Comm,0)) Desc;

--查询allen上司的信息
--方法1，嵌套查询
Select Empno,Ename,Hiredate
From Emp
Where Empno=(
  Select Mgr From Emp
  Where  Ename='ALLEN');
--方法2，自连接查询
Select M.Empno,M.Ename,M.Hiredate
From Emp E,Emp M 
Where E.Mgr = M.Empno
And e.Ename='ALLEN';

/*
连接：左外连接，右外连接，满外连接
可以一次用【关键字】 
left outer join
right outer join
full outer join
用+号区分时，没有+的是主表，带的是外连接表
*/
/* 左外连接 */
--形式1
Select Empno,Ename,Dname,Job
From Emp 
Left Outer Join Dept 
On Emp.Deptno = Dept.Deptno; 
--形式2
Select Empno,Ename,Dname,Job
From Emp,Dept
where Emp.Deptno = Dept.Deptno(+); 

/* 右外连 */
--形式1
Select Empno,Ename,Dname 
From Emp 
Right Outer Join Dept 
On Emp.Deptno = Dept.Deptno;
--形式2
Select Empno,Ename,Dname
From Emp,Dept
where Emp.Deptno(+) = Dept.Deptno; 

--满外连
Select Empno,Ename,Dname 
From Emp 
Full Outer Join Dept 
On Emp.Deptno = Dept.Deptno; 

/* 表可以外部连接到至多一个其它的表 所以下面这个语句是错误的*/
Select Empno,emp.Ename,Dname,Bonus.Sal
From Emp,Dept,Bonus
Where Emp.Deptno(+) = Dept.Deptno
And  Emp.Job(+)=Bonus.Job; 
--而下面这样写却是可以的
Select Empno,emp.Ename,Dname,emp.Sal
From Emp,Dept,Bonus
Where Emp.Deptno(+) = Dept.Deptno
And  Emp.Job=Bonus.Job(+); 

Select *  From  Emp
Where Sal>Any(
  Select Avg(Sal) From Emp 
  Group By Deptno
);

/*  可能会存在null行*/
Select avg(sal) From emp group by Deptno;
  
Select * From Emp
Where Sal>all(
  Select Avg(Sal) From Emp 
  Group By Deptno
  Having avg(sal)>0
);

/* 
oracle 中的分页是通过隐式字段rownum来获取行标，从而进行分段取数据
*/

--取前5条记录，Rownum是每个表的隐藏字段
Select Rownum,Empno,Ename From Emp
Where Rownum >=1 And Rownum <=5;

/* 
取emp表6-10行数据可以做如下处理
注意这个rownum隐式字段一定要起个别名如rn
否则也无法进行访问
*/
Select * From
( Select Rownum Rn,Empno,Ename From Emp) Temptable/* 隐式表 */
Where Temptable.Rn >=6 And Temptable.Rn <=10;/*引用别名来实现分页*/

/* 取emp表11-15行数据可以做如下处理 */
Select * From
( Select Rownum Rn,Empno,Ename From Emp) Temptable/* 隐式表 */
Where Temptable.Rn >=11 And Temptable.Rn <=15;