oracle表复杂查询

在实际应用中经常需要执行复杂的数据统计，经常需要显示多张表的数据，现在我们给大家介绍较为复杂的select语句

n  数据分组  max  min  avg  sum  count

?       如何显示所有员工中最高工资和最低工资

select max(sal),min(sal) from emp;

?       显示所有员工的平均工资和工资总和

select avg(sal),sum(sal) from emp;

☞avg(comm)不会把comm为null的行进行统计，因此我们要注意，如果希望为null的也考虑，则我们可以这样做：

select sum(comm)/count(*) form emp；

?       计算共有多少员工

select count(*) from emp;

细节：count（*），也可以对一个字段进行统计，比如：count(sal)；

      count(comm)不考虑为null进行统计。

n  扩展要求：

?       请显示工资最高的员工的名字，工作岗位

select ename,job from emp where sal=(select max(sal) from emp);

?       请显示工资高于平均工资的员工信息

select * from emp where sal>(select  avg(sal)  from emp);

select 语句执行的顺序

（1）我们写SQL语句是从左到右

（2）SQL执行在默认情况下是从右向左执行。

（3）无论select有多少，oracle有执行顺序。

n  group by和having字句

group by 用于对查询的结果分组统计   having字句用于限制(过滤)分组显示结果

?       如何显示每个部门的平局工资和最高工资

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

?       显示每个部门的每种岗位的平均工资和最低工资

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

?       显示平均工资低于2000的部门号和它的平均工资

select deptno,avg(sal) from emp group by deptno having avg(sal)<2000;

having不能使用别名

SQL> select deptno,avg(sal) myavg from emp group by deptno having myavg<2000;

select deptno,avg(sal) myavg from emp group by deptno having myavg<2000

ORA-00904: "MYAVG": 标识符无效

n  对数据分组的总结

1、分组函数（avg...）只能出现在选择列表、having、order by字句中

select avg(sal),deptno from emp group by deptno having avg(sal)>100 order by avg(sal) desc;

2、如果在select语句中同时包含有group  by，having，order  by，那么它们的顺序是group by，having，order by。

3、在选择列中如果有列、表达式、分组函数，那么这些列和表达式必须有一个出现在group by字句中，否则就会出错

如select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal)<2000;

这里deptno就一定要出现在group by中。

SQL> select avg(sal),deptno,job from emp group by job having avg(sal)>100 order by avg(sal);

select avg(sal),deptno,job from emp group by job having avg(sal)>100 order by avg(sal)

ORA-00979: 不是 GROUP BY 表达式

SQL> select avg(sal),deptno,job from emp group by job,deptno having avg(sal)>100 order by avg(sal);

  AVG(SAL) DEPTNO JOB

---------- ------ ---------

       950     30 CLERK

       950     20 CLERK

      1300     10 CLERK

      1400     30 SALESMAN

      2450     10 MANAGER

      2850     30 MANAGER

      2975     20 MANAGER

      3000     20 ANALYST

      5000     10 PRESIDENT

9 rows selected