欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

SQL复杂查询(一)

程序员文章站 2024-03-04 20:57:36
...

1.列出至少有一个员工的所有部门编号、名称,并统计出这些部门的平均工资、最低工资、最高工资。

确定所需要的数据表
emp表:员工的部门编号,平均工资,最低工资,最高工资
dept表:部门名称

确定已知的关联字段
emp.deptno=dept.deptno

1.1 先得到员工数大于1的部门

SELECT deptno,COUNT(empno) FROM emp GROUP BY deptno HAVING COUNT(empno)>1;  

SQL复杂查询(一)

1.2 找到部门名称

SELECT d.dname,COUNT(e.empno) FROM emp e ,dept d 
     WHERE e.deptno=d.deptno GROUP BY d.deptno,d.dname     HAVING COUNT(e.empno)>1;

SQL复杂查询(一)

1.3 统计各种信息
SQL分组:http://blog.csdn.net/dingchenxixi/article/details/70193634
select子句之后,只能出现分组的字段和统计函数,其它的字段不能出现
由于AVG(),MAX(),MIN()为统计函数,可以直接使用

SELECT d.deptno,d.dname,COUNT(e.empno),AVG(sal),MIN(sal),MAX(sal) 
   FROM emp e ,dept d  
   WHERE e.deptno=d.deptno 
   GROUP BY d.deptno,d.dname HAVING COUNT(e.empno)>1;

SQL复杂查询(一)

2.列出薪金比“SMITH”或“ALLEN”多的所有员工的编号、姓名、部门名称、其领导姓名。
确定所需要的数据表
emp表:列出“SMITH”或“ALLEN”的薪金
emp表:员工的编号,姓名
dept表:部门名称

确定已知的关联字段
emp.deptno=dept.deptno;
emp1.mgr = emp2.empno (自关联查询)

2.1 先查询出“SMITH”或“ALLEN”的薪金

SELECT sal FROM emp WHERE ename='SMITH' OR ename='ALLEN'

或者

SELECT sal FROM emp WHERE ename IN('SMITH','ALLEN');

SQL复杂查询(一)

2.2 子查询,查询比“SMITH”或“ALLEN”薪金多的员工编号,姓名,部门名称

SELECT e.empno,e.ename,e.sal,d.dname FROM emp e,dept d WHERE e.sal>ANY(SELECT sal FROM emp WHERE ename='SMITH' OR ename='ALLEN') 
      AND e.deptno=d.deptno;

SQL复杂查询(一)

2.3 自身关联查询

SELECT e.empno,e.ename,e.sal,d.dname,m.ename leader FROM  emp e,emp m,dept d 
      WHERE e.sal>ANY(SELECT sal FROM emp WHERE           ename='SMITH' OR ename='ALLEN') 
      AND e.deptno=d.deptno AND e.mgr=m.empno(+);

SQL复杂查询(一)

3.列出所有员工的编号、姓名及其直接上级的编号、姓名,显示的结果按领导年工资的降序排列

确定所需要的数据表
emp表1:员工的编号,姓名,上级的编号
emp表2:上级的姓名,领导的年工资

确定已知的关联字段
emp1.mgr=emp2.empno

SELECT e.empno,e.ename,m.ename leader,m.empno leader_no , NVL((m.sal+NVL(m.comm,0))*12 ,0) INCOME FROM emp e,emp m  
       WHERE e.mgr=m.empno(+)  ORDER BY INCOME DESC ;

SQL复杂查询(一)

4.列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数

确定所需要的数据表:
emp表:员工的编号,姓名
dept表:部门名称,部门位置
emp表:部门人数

确定已知的关联字段:
emp.deptno=dept.deptno 部门人数
emp1.mgr = emp2.empno 上级领导

4.1 受雇日期早于其直接上级的所有员工的编号,姓名

SELECT e.empno,e.ename FROM       
      emp e,emp m WHERE e.mgr=m.empno AND e.hiredate<m.hiredate;

SQL复杂查询(一)

4.2 列出满足条件的部门名称、部门位置

 SELECT e.empno,e.ename,d.dname ,d.loc 
       FROM emp e,emp m,dept d 
       WHERE e.mgr=m.empno AND e.hiredate<m.hiredate AND e.deptno=d.deptno;

SQL复杂查询(一)

4.3 统计部门人数,此时由于要使用统计函数,而以上的查询无法再直接出现统计函数,所以使用子查询完成

SELECT e.empno,e.ename,d.dname ,d.loc ,temp.count
       FROM emp e,emp m,dept d ,(SELECT deptno,COUNT(empno) count FROM emp GROUP BY deptno) temp
       WHERE e.mgr=m.empno AND e.hiredate<m.hiredate AND e.deptno=d.deptno AND e.deptno=temp.deptno;

SQL复杂查询(一)

5.列出部门名称和这些部门的员工信息(数量、平均工资),同时列出那些没有员工的部门

确定所需要的数据表:
dept表:部门名称
emp表:员工平均工资
emp表:统计员工数量

确定已知的关联字段:
emp.deptno=dept.deptno

SELECT d.dname,COUNT(e.empno),AVG(e.sal) 
    FROM dept d ,emp e 
    WHERE e.deptno(+)=d.deptno GROUP BY d.dname;

SQL复杂查询(一)

相关标签: sql 复杂查询