oracle练习之子查询

最新推荐文章于 2022-01-21 21:15:23 发布

杨恒_

最新推荐文章于 2022-01-21 21:15:23 发布

阅读量2k

点赞数 1

分类专栏： oracle 文章标签： oracle 关联查询-子查询

本文链接：https://blog.csdn.net/qq_37643150/article/details/68927083

版权

oracle 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

子查询
问题1 ：按工资进行排名，排名从1开始，工资相同则排名相同，（如果两人并列第一则没有第二名从第三名开始继续排）
select e.* , (select count(*) from emp where sal>e.sal ) +1 pm from emp e order by pm ;

        SQL> select e.* , (select count(*) from emp where sal>e.sal ) +1 pm from emp e order by pm ;

             EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO         PM
        ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- ----------
              7839 KING       PRESIDENT            17-11月-81           5000                    10          1
              7902 FORD       ANALYST         7566 03-12月-81           3000                    20          2
              7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20          2
              7566 JONES      MANAGER         7839 02-4月 -81           2975                    20          4
              7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30          5
              7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10          6
              7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30          7
              7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30          8
              7934 MILLER     CLERK           7782 23-1月 -82           1300                    10          9
              7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30         10
              7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30         10
              7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20         12
              7900 JAMES      CLERK           7698 03-12月-81            950                    30         13
              7369 SMITH      CLERK           7902 17-12月-80            800                    20         14

        已选择14行。

问题2：求入职时间相同的员工
select * from emp e where (select count(*) from emp where hiredate=e.hiredate ) >=2 ;

        SQL> select * from emp e where (select count(*) from emp where hiredate=e.hiredate ) >=2 ;

             EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
        ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
              7900 JAMES      CLERK           7698 03-12月-81            950                    30
              7902 FORD       ANALYST         7566 03-12月-81           3000                    20

问题3 ：查询每个部门工资最高的前两名（题目解析：在一个部门内找员工，要求比该员工工资高的人的人数为0或1 ）
select * from emp e where (select count(*) from emp where sal >e.sal and deptno=e.deptno) <2 ;

        SQL> select * from emp e where (select count(*) from emp where sal >e.sal and e.deptno=deptno) <2 ;

             EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
        ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
              7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30
              7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30
              7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10
              7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20
              7839 KING       PRESIDENT            17-11月-81           5000                    10
              7902 FORD       ANALYST         7566 03-12月-81           3000                    20

        已选择6行。

问题4 ：查询工资相同的员工的工资和姓名
select e.sal ,e.ename from emp e where (select count(*) from emp where sal=e.sal and ) >=2 ;

            SQL> select * from emp t1 where (select count(*) from emp where sal= t1.sal ) >=2;

                 EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
            ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
                  7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
                  7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30
                  7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20
                  7902 FORD       ANALYST         7566 03-12月-81           3000                    20

显示出和员工号7369部门相同的员工姓名，工资
列出至少有二个雇员的所有部门

Select deptno From emp group by deptno having count(*)>2;

列出薪金比”SMITH”多的所有雇员

Select ename From emp where sal>(select sal from emp where ename=’SMITH’);

列出入职日期早于其直接上级的所有雇员

Select * From emp e,(select empno,hiredate from emp) a where e.mgr=a.empno and e.hiredate

ENAME

ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES

已选择6行。

显示出工资大于平均工资的员工姓名，工资

Select e.ename,e.sal From emp e,(select avg(sal) a from emp) s where e.sal>s.a;

                    SQL> select e.ename ,e.sal from emp e where sal>(select avg(sal) from emp );

                    ENAME             SAL
                    ---------- ----------
                    JONES            2975
                    BLAKE            2850
                    CLARK            2450
                    SCOTT            3000
                    KING             5000
                    FORD             3000

                    已选择6行。

                    SQL> select e.ename ,e.sal from emp e, (select avg(sal ) a from emp ) s where e.sal > s.a;

                    ENAME             SAL
                    ---------- ----------
                    JONES            2975
                    BLAKE            2850
                    CLARK            2450
                    SCOTT            3000
                    KING             5000
                    FORD             3000

                    已选择6行。

显示出工资大于本部门平均工资的员工姓名，工资

Select e.ename,e.sal from emp e,(select deptno,avg(sal) av from emp group by deptno) a where e.deptno=a.deptno and sal>av;

显示员工”KING”所管理的员工姓名

Select e.ename from emp e,(select empno from emp where ename=’KING’) a where e.mgr=a.empno;

显示每位经理管理员工的最低工资，及最低工资者的姓名

Select e.ename,e.sal From emp e,(select min(sal) mins from emp group by mgr having mgr is not null) a where e.sal=a.mins;

显示比工资最高的员工参加工作时间晚的员工姓名，参加工作时间

Select ename,hiredate From emp where hiredate>(select hiredate from emp where sal=(select max(sal) from emp));

显示出平均工资最高的的部门平均工资及部门名称

Select d.dname,a.s From dept d,(select * from (select deptno,avg(sal) s from emp group by deptno order by avg(sal) desc) where rownum=1) a where d.deptno=a.deptno;