牛客网数据库SQL实战剖析(11-20)
11. 获取所有员工当前的manager
获取所有员工当前的manager,如果当前的manager是自己的话结果不显示,当前表示to_date='9999-01-01'。结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_no。
create table `dept_emp` ( `emp_no` int(11) not null, `dept_no` char(4) not null, `from_date` date not null, `to_date` date not null, primary key (`emp_no`,`dept_no`) ); create table `dept_manager` ( `dept_no` char(4) not null, `emp_no` int(11) not null, `from_date` date not null, `to_date` date not null, primary key (`emp_no`,`dept_no`) );
解析:首先要查询员工的领导,只需要dept_emp
左连接dept_manager
。此外还有两个条件,当前的manager是自己的话结果不显示,以及to_date='9999-01-01'
,将其写成 where
子句。
select e.emp_no, m.emp_no as manager_no from dept_emp e left join dept_manager m on e.dept_no = m.dept_no where e.to_date = '9999-01-01' and m.to_date = '9999-01-01' and e.emp_no != m.emp_no;
12. 获取所有部门中当前员工薪水最高的相关信息
获取所有部门中当前员工薪水最高的相关信息,给出dept_no
, emp_no
以及其对应的salary
。
create table `dept_emp` ( `emp_no` int(11) not null, `dept_no` char(4) not null, `from_date` date not null, `to_date` date not null, primary key (`emp_no`,`dept_no`)); create table `salaries` ( `emp_no` int(11) not null, `salary` int(11) not null, `from_date` date not null, `to_date` date not null, primary key (`emp_no`,`from_date`));
解题思路:首先按照部门进行分组,然后用 max()
函数获取部门中工资最大值。
select d.dept_no, s.emp_no, max(s.salary) as salary from dept_emp d join salaries s on d.emp_no = s.emp_no where s.to_date = '9999-01-01' and d.to_date = '9999-01-01' group by d.dept_no;
13. 从titles表获取按照title进行分组
从titles表获取按照title进行分组,每组个数大于等于2,给出title以及对应的数目t。
create table if not exists "titles" ( `emp_no` int(11) not null, `title` varchar(50) not null, `from_date` date not null, `to_date` date default null);
解题思路:分组使用group by
,分组之后还要判断每组个数,则用 having count(*)
。
select title, count(*) as t from titles group by title having t >= 2;
14. 从titles表获取按照title进行分组,注意对于重复的emp_no进行忽略。
从titles表获取按照title进行分组,每组个数大于等于2,给出title以及对应的数目t。
注意对于重复的emp_no进行忽略。
create table if not exists `titles` ( `emp_no` int(11) not null, `title` varchar(50) not null, `from_date` date not null, `to_date` date default null);
解题思路:在13题基础上,原来是统计整个组的格式,本题要根据 emp_no
进行去重,所以使用 count(distinct emp_no)
。
select title, count(distinct emp_no) as t from titles group by title having t >= 2;
15. 查找employees表
查找employees表所有emp_no为奇数,且last_name不为mary的员工信息,并按照hire_date逆序排列
create table `employees` ( `emp_no` int(11) not null, `birth_date` date not null, `first_name` varchar(14) not null, `last_name` varchar(16) not null, `gender` char(1) not null, `hire_date` date not null, primary key (`emp_no`));
解题思路:这道题思路很直接,emp_no
为奇数,即取余之后不等于0,last_name != 'mary'
,再用order by xxx desc
逆序排列。
select * from employees where emp_no%2 != 0 and last_name != 'mary' order by hire_date desc ;
16. 统计出当前各个title类型对应的员工当前薪水对应的平均工资
统计出当前各个title类型对应的员工当前(to_date='9999-01-01')薪水对应的平均工资。结果给出title以及平均工资avg。
create table `salaries` ( `emp_no` int(11) not null, `salary` int(11) not null, `from_date` date not null, `to_date` date not null, primary key (`emp_no`,`from_date`)); create table if not exists "titles" ( `emp_no` int(11) not null, `title` varchar(50) not null, `from_date` date not null, `to_date` date default null);
解题思路:这道题和第12题是一个意思,先通过 title
分组,然后用 avg()
求工资平均值。
select t.title, avg(s.salary) from titles t join salaries s on t.emp_no = s.emp_no where t.to_date='9999-01-01' and s.to_date='9999-01-01' group by t.title;
17. 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
获取当前(to_date='9999-01-01')薪水第二多的员工的emp_no以及其对应的薪水salary
create table `salaries` ( `emp_no` int(11) not null, `salary` int(11) not null, `from_date` date not null, `to_date` date not null, primary key (`emp_no`,`from_date`));
解题思路:这道题前面有类似的,在子查询里面,通过排序查出第二多的薪水。然后查询该薪水的员工。
select emp_no, salary from salaries where salary = ( select salary from salaries where to_date = '9999-01-01' order by salary desc limit 1,1 );
但看输出样例只有一行记录,感觉题目本意应该是薪水第二多的人只有一个,让我们直接查询之后用薪水进行排序,再逆序查到这个人。
select emp_no, salary from salaries order by salary desc limit 1,1;
两种都能ac,第二种应该是题目想要我们给出的答案。
18. 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary,不准使用order by
查找当前薪水(to_date='9999-01-01')排名第二多的员工编号emp_no、薪水salary、last_name以及first_name,不准使用order by
create table `employees` ( `emp_no` int(11) not null, `birth_date` date not null, `first_name` varchar(14) not null, `last_name` varchar(16) not null, `gender` char(1) not null, `hire_date` date not null, primary key (`emp_no`)); create table `salaries` ( `emp_no` int(11) not null, `salary` int(11) not null, `from_date` date not null, `to_date` date not null, primary key (`emp_no`,`from_date`));
解题思路:薪水第二多的,就是除掉薪水最多的数据之后,新数据集中,薪水最多的。说法有点绕,看sql就能看明白。
select e.emp_no, max(s.salary) as salary, e.last_name, e.first_name from employees e join salaries s on e.emp_no = s.emp_no where s.to_date = '9999-01-01' and s.salary < (select max(salary) from salaries where to_date = '9999-01-01');
19. 查找所有员工的last_name和first_name以及对应的dept_name
查找所有员工的last_name和first_name以及对应的dept_name,也包括暂时没有分配部门的员工
create table `departments` ( `dept_no` char(4) not null, `dept_name` varchar(40) not null, primary key (`dept_no`)); create table `dept_emp` ( `emp_no` int(11) not null, `dept_no` char(4) not null, `from_date` date not null, `to_date` date not null, primary key (`emp_no`,`dept_no`)); create table `employees` ( `emp_no` int(11) not null, `birth_date` date not null, `first_name` varchar(14) not null, `last_name` varchar(16) not null, `gender` char(1) not null, `hire_date` date not null, primary key (`emp_no`));
解题思路:本题考察三个表的关联查询,departments
和employees
通过中间表dept_emp
关联在一起。
select e.last_name, e.first_name, d.dept_name from employees e left join dept_emp de on e.emp_no = de.emp_no left join departments d on de.dept_no = d.dept_no;
20. 查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth
查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth
create table `salaries` ( `emp_no` int(11) not null, `salary` int(11) not null, `from_date` date not null, `to_date` date not null, primary key (`emp_no`,`from_date`));
解题思路:涨幅即最大值-最小值,简单这样理解即可。
select max(salary)-min(salary) as growth from salaries where emp_no=10001;