动力节点试题练习26题

26.列出在每个部门工作的员工数量、平均工资和平均服务期限

1 select 
2 e.deptno,
3 count(e.ename)as"员工数量", 
4 avg(e.sal) as avgsal,
5 ((avg(to_days(now())-to_days(e.hiredate)))/365)
6 from emp e
7 group by deptno

1 select 2 e.deptno, 3 count(e.ename)as"员工数量", 4 avg(e.sal) as avgsal, 5 ((avg(to_days(now())-to_days(e.hiredate)))/365) 6 from emp e 7 group by deptno

以上为错误解法 --------------------------------------------------------------------------- 第一步 求出每个部门对应的所有员工，这里使用了右连接，保证显示所有的部门，但是有的部门不存在员工，但是也必须把所有的部门显示出来，将员工表与部门表进行右连接，将信息全部显示

select 
e.*,d.*
from 
emp e
right join 
dept d
on e.deptno=d.deptno;

select e.*,d.* from emp e right join dept d on e.deptno=d.deptno;

第二步 在此基础上列出每个部门工作的员工数量

select 
d.deptno,count(e.ename)
from 
emp e
right join 
dept d
on e.deptno=d.deptno
group by 
d.deptno;

select d.deptno,count(e.ename) from emp e right join dept d on e.deptno=d.deptno group by d.deptno;

第三步，在以上基础上，列出每个员工的平均工资

select 
d.deptno,count(e.ename),ifnull(avg(e.sal),0)as avgsal
from 
emp e
right join 
dept d
on e.deptno=d.deptno
group by 
d.deptno;

select d.deptno,count(e.ename),ifnull(avg(e.sal),0)as avgsal from emp e right join dept d on e.deptno=d.deptno group by d.deptno;

第四步，在以上基础上，列出没个员工的平均服务期限 1）to_days（）函数，计算出天数

select to_days(now());

select to_days(now());

换算为年数

select to_days(now())/365

select to_days(now())/365

2)计算员工在公司的服务期限

select (to_days(now())-to_days(hiredate))/365 as servertime
from emp;

select (to_days(now())-to_days(hiredate))/365 as servertime from emp;

整体语句

select 
d.deptno,
count(e.ename) as totalemp,
ifnull(avg(e.sal),0)as avgsal,
avg(ifnull((to_days(now())-to_days(hiredate))/365,0)) as avgservertime
from 
emp e
right join 
dept d
on e.deptno=d.deptno
group by 
d.deptno;

select d.deptno, count(e.ename) as totalemp, ifnull(avg(e.sal),0)as avgsal, avg(ifnull((to_days(now())-to_days(hiredate))/365,0)) as avgservertime from emp e right join dept d on e.deptno=d.deptno group by d.deptno;

26.列出在每个部门工作的员工数量、平均工资和平均服务期限 1 select 2 e.deptno, 3 count(e.ename)as"员工数量", 4 avg(e.sal) as avgsal, 5 ((avg(to_days(now())-to_days(e.hiredate)))/365) 6 from emp e 7 group by deptno 以上为错误解法 --------------------------------------------------------------------------- 第一步 求出每个部门对应的所有员工，这里使用了右连接，保证显示所有的部门，但是有的部门不存在员工，但是也必须把所有的部门显示出来，将员工表与部门表进行右连接，将信息全部显示 select e.*,d.* from emp e right join dept d on e.deptno=d.deptno; 第二步 在此基础上列出每个部门工作的员工数量 select d.deptno,count(e.ename) from emp e right join dept d on e.deptno=d.deptno group by d.deptno; 第三步，在以上基础上，列出每个员工的平均工资 select d.deptno,count(e.ename),ifnull(avg(e.sal),0)as avgsal from emp e right join dept d on e.deptno=d.deptno group by d.deptno; 第四步，在以上基础上，列出没个员工的平均服务期限 1）to_days（）函数，计算出天数 select to_days(now()); 换算为年数 select to_days(now())/365 2)计算员工在公司的服务期限 select (to_days(now())-to_days(hiredate))/365 as servertime from emp; 整体语句 select d.deptno, count(e.ename) as totalemp, ifnull(avg(e.sal),0)as avgsal, avg(ifnull((to_days(now())-to_days(hiredate))/365,0)) as avgservertime from emp e right join dept d on e.deptno=d.deptno group by d.deptno;