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

一、组函数和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;

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

 

 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值