MySQL(二)

牛客网题霸SQL篇(011-020)

---

011

题目描述：获取所有的员工和员工对应的经理，如果员工本身是经理的话则不显示。

背景描述：

员工表

部门经理表

题解：

select e.emp_no,m.emp_no as manager 
from dept_emp e 
inner join dept_manager1 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

运行结果：

注意：

1. 用 INNER JOIN 连接两张表，因为要输出自己的经理编号，得知自己与经理的部门要相同，故有限制条件 e.dept_no = m.dept_no
2. 再用 WHERE 限制当前员工与当前经理的条件，即 m.to_date 等于 '9999-01-01' 、e.to_date 等于 '9999-01-01' 、 e.emp_no 不等于 m.emp_no
3. 为了增强代码可读性，将 dept_emp 用别名 e 代替，dept_manager 用 m 代替，最后根据题意将 de.emp_no 用别名 manager代替后输出

012

题目描述：获取每个部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号升序排列。

背景描述：

员工表

薪水表

题解：

select r.dept_no,ss.emp_no,r.maxSalary from (
select d.dept_no,max(s.salary)as maxSalary from dept_emp d,salaries s
where d.emp_no=s.emp_no
and d.to_date='9999-01-01' 
and s.to_date='9999-01-01'
group by d.dept_no
)as r,salaries ss,dept_emp dd
where r.maxSalary=ss.salary
and r.dept_no=dd.dept_no
and dd.emp_no=ss.emp_no
and ss.to_date='9999-01-01'
and dd.to_date='9999-01-01'
order by r.dept_no asc；

运行结果：

013

题目描述：查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列。

背景描述：

员工表

题解：

select * from employees where emp_no & 1 and last_name !='Mary' order by hire_date desc;

select * from employees where emp_no%2=1 and last_name !='Mary' order by hire_date desc;

运行结果：

补充：

• 不相等有三种表示方式：<>、!=、IS NOT；
• 查询奇数的表示方式：%取余；&1：1的二进制是0001，与运算符：1 & 1 = 1, 1 & 0 = 0, 0 & 1 = 0, 0 & 0 = 0；所以可以保证最后一位是奇数。

014

题目描述：请你统计出各个title类型对应的员工薪水对应的平均工资avg。结果给出title以及平均工资avg，并且以avg升序排序。

员工职称表

薪水表

题解：

select t.title,avg(s.salary) as avg 
from salaries s 
join titles t 
on s.emp_no = t.emp_no 
and t.to_date='9999-01-01' 
and s.to_date='9999-01-01' 
group by title;

运行结果：

015