oracle一道简单面试题和一道深入面试题

r囧r小猫 2022-06-04 03:08 317阅读 0赞

> 练习使用的是Oracle提供的scott 用户中的表

–找到员工表中薪水大于本部门平均薪水的员工

oracle提供的部门表,但是部门里没有平均工资,所以我们不链接它提供的表,自己查一张表出来
    找出所有部门,及平均工资
    select deptno, avg(sal) from emp group by deptno;
    
    员工根据部门链接刚刚查询的表,比较工资,大于的话就打印
    select * from emp e, (select deptno, avg(sal) avgSal from emp group by deptno) d where e.deptno = d.deptno --判断部门 and e.sal > d.avgSal;    --判断工资

–统计每年入职员工的个数

--统计每年入职员工的个数
    1.查询年 
    select to_char(hiredate,'yyyy') year from emp;
    
    2.根据年分组,然后统计数量
    select to_char(hiredate,'yyyy') year ,count(*) 人数 from emp group by to_char(hiredate,'yyyy');

![这里写图片描述][SouthEast]  
实际上已经完成了 , 但是要求我们达到以下的效果

![这里写图片描述][SouthEast 1]

我们先把1987竖起来,

select case year when '1987' then 人数 end from (select to_char(hiredate, 'yyyy') year, count(*) 人数 from emp group by to_char(hiredate, 'yyyy'));

结果如下  
![这里写图片描述][SouthEast 2]

我们把多行变成一行,使用聚合函数,聚合函数会忽略空值  
所以随便使用一个聚合函数就行,,

select avg(case year when '1987' then 人数 end) year from (select to_char(hiredate, 'yyyy') year, count(*) 人数 from emp group by to_char(hiredate, 'yyyy'));

运行结果如下  
![这里写图片描述][SouthEast 3]  
题目还需要我们扩充一列总人数

select sum(人数) total, avg(case year when '1987' then 人数 end) year from (select to_char(hiredate, 'yyyy') year, count(*) 人数 from emp group by to_char(hiredate, 'yyyy'));

运行结果如下  
![这里写图片描述][SouthEast 4]

最后终极版的代码如下 ,

select sum(人数) total, avg(case year when '1980' then 人数 end) "1980", avg(case year when '1981' then 人数 end) "1981", avg(case year when '1982' then 人数 end) "1982" , avg(case year when '1987' then 人数 end) "1987" from (select to_char(hiredate, 'yyyy') year, count(*) 人数 from emp group by to_char(hiredate, 'yyyy'));

![这里写图片描述][SouthEast 5]

[SouthEast]: /images/20220604/8251fadc2ac8428ebbfacac3d4f60016.png
[SouthEast 1]: /images/20220604/5ce1cae7f6894452a9c80c95b1f6b3db.png
[SouthEast 2]: /images/20220604/dc7dd403f13145a0878de369de1ecb6f.png
[SouthEast 3]: /images/20220604/7b1e6d6e3340415c8c333755d941d157.png
[SouthEast 4]: /images/20220604/208f7137e4674edaa12654e647844eae.png
[SouthEast 5]: /images/20220604/d28ffd2299d844f4a8a3191a9a78d0d1.png