mysql牛客网_牛客网数据库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;

如您对本文有疑问或者有任何想说的，请点击进行留言回复，万千网友为您解惑！