终端—复杂子查询语句汇总

一、组函数和group by 子句的基础

      ①将多条记录组合在一起，产生一个输出，即函数的输入有N条， 输出仅有一条；

      ②代表函数有： mix, min, sum, count, avg

      ③在有group by子句的SQL语句中，所有出现在select后面的字段，如果没有出现在group by 子句后面，则必须出现在组函数中。如： select deptno, max(sal) from emp group by deptno;

二、子查询语句汇总

     1. 查询每个部门工资最高的人的信息：

    方法一：

select e.deptno,e.ename,e.sal from emp e,

(select deptno,max(sal) sal from emp group by deptno) t

where e.deptno=t.deptno and e.sal=t.sal order by e.deptno;

        

      方法二：

 select ename,e.sal,e.deptno from emp e join

            (select max(sal) sal, deptno from emp group by deptno) t

on e.sal=t.sal and e.deptno=t.deptno;

       注意：inner join ... on...相当于join... on...，为了让where只做选择条件，on做连接条件，推荐使用第二种方法进行表的连接操作。

        

    2、查询每个员工姓名、部门名称、工资等级，并筛选以A开头的名字：

select ename, dname, grade from emp e  join dept d on e.deptno=d.deptno   join salgrade on e.sal between losal and hisal   where ename like 'A%';

     

   3、 求部门平均薪水的等级：

select e.deptno,d.dname, e.avg_sal, grade from  

     (select deptno, avg(sal) avg_sal from emp group by deptno) e 

 join dept d on e.deptno=d.deptno  join salgrade s on avg_sal between s.losal and hisal;

   

4、 雇员中有哪些人是经理人： select ename 经理人 from emp where empno in (select distinct mgr from emp);

    ①只查询经理即可；

    ②父查询和子查询中，如果没有相同的查询属性值，则不会起冲突，可不起别名；

    ③利用distinct过滤掉重复的值；

5、 不准用组函数，求薪水的最高值：

select e.sal from emp e where e.sal not in

         (  select distinct e1.sal from emp e1 join emp e2 on e1.sal<e2.sal);

     ①首先将emp表自连接，找出所有的左边的表的sal值小于右边的表的sal值，进行一次类似于笛卡尔积的连接；

    ②左边表的最大值在右边表中找不到适合的匹配，被遗漏出来，我们捡漏即可找出最大值；

    ③因为此连接时类似于笛卡尔积的连接，左边的一条记录会同时和右边的多条记录进行连接，所以需要利用distinct过滤掉重复的值；

     

6、 求平均薪水最高的部门的部门编号：

方法一：

select deptno, avg_sal from

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

where avg_sal=

     (select max(avg_sal) from

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

方法二：

select deptno, avg_sal from

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

where avg_sal=

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

      ①先求出每个部门的平均工资，以及部门编号；

      ②根据求出来的平均工资，计算平均工资里面的最大值，而后利用该最大值与每一个部门的平均工资进行比较，即可求出平均工资最高点的部门编号；

          

          

7、 求平均薪水最高的部门的部门名称：

select e.deptno,d.dname ,avg_sal from

    (select deptno, avg(sal) avg_sal from emp group by deptno) e

join dept d on e.deptno=d.deptno where avg_sal=

    (select max(avg_sal) from

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

      ①子查询中相同的属性值可以不起别名不指定，但是在连接查询中，如果两个表有相同的属性列，必须确切指定属性列属于哪张表。

8、 求平均薪水的等级最低的部门的部门名称：

select t.deptno, dname, avg_sal, grade from
   (
      select deptno, avg_sal, grade from
        (
           select deptno,avg(sal) avg_sal from emp group by deptno
        )e
      join salgrade on (avg_sal between losal and hisal)
   )t
join dept d on d.deptno=t.deptno
where grade=
   (
       select min(grade) from 
          (
             select deptno, avg_sal, grade from
              (
                 select deptno,avg(sal) avg_sal from emp group by deptno
              )e
             join salgrade on (avg_sal between losal and hisal)
          )
   )；

     ①可以使用视图来简化该SQL语句，视图就是一个子查询或者说就是一张虚表，原来是从表（子查询）里取数据，现在从视图

里面取数据；

    ②由于当下使用scott用户来操作数据表，其没有创建表的权限，我们先登录系统用户sys/ system，为其分配创建表的权限后再登录scott用户，创建视图；

      

      

      ③从视图中取数据，简化如上代码如下：

     select t.deptno, dname, avg_sal, grade from v$_dept_avg_sal_info t

    join dept d on t.deptno=d.deptno where t.grade=

       (select min(grade) from v$_dept_avg_sal_info);

     

9、 求比普通员工的最高薪水还要高的经理人姓名:

    方法一：

select empno,ename,sal from emp where empno in

     (select distinct mgr from emp where mgr is not null)

and sal>

     (select max(sal) from emp where empno not in

          (select distinct mgr from emp where mgr is not null));

   方法二：

 select distinct m.empno, m.ename, m.sal from

      (select e1.empno, e1.ename, e1.sal from emp e1 join emp e2 on e1.empno=e2.mgr) m

where m.sal>

      (select max(sal) from emp where empno not in

           (select distinct mgr from emp where mgr is not null)

      );

方法三：

 select empno,ename,sal from emp where empno in

     (select distinct mgr from emp where mgr is not null)

and sal>

      (select max(sal) from

                (select sal from emp where empno not in

                      (select distinct mgr from emp where mgr is not null)

                 )

       );

10、 求薪水最高的前五名雇员：

       select ename, sal from

              (select ename,sal from emp where sal is not null order by sal desc)

      where rownum<=5;

     

错误写法：

   

11、 求薪水最高的第6到第10名雇员：

 select R, ename, sal from

     (select rownum R, ename,sal from

           (select ename, sal from emp where sal is not null order by sal desc)

     )

where R>=6 and R<=10;

    ①这种三层select嵌套方法在Oracle里面处理此类问题时，是效率最高的；

     

12、 求个部门的平均薪水等级： select t.deptno,avg(t.grade) from (select deptno,ename,grade from emp e join salgrade s on (e.sal between s.losal and s.hisal)) t group by t.deptno;

       ①先求出公司中所有人的工资等级和所属部门编号，再根据部门编号分组，将等级作为基数求平均值。