牛客网sql实战:11-20

11.获取所有员工当前的(dept_manager.to_date=‘9999-01-01’)manager，如果员工是manager的话不显示(也就是如果当前的manager是自己的话结果不显示)。输出结果第一列给出当前员工的emp_no,第二列给出其manager对应的emp_no。

select de.emp_no,dm.emp_no manager_no
from dept_emp as de left join dept_manager as dm
on de.dept_no=dm.dept_no
where dm.to_date='9999-01-01' 
and de.to_date='9999-01-01'
and de.emp_no<>dm.emp_no 

• 做到这题算是了解了to_date和from_date的含义…to_date='9999-01-01’的话就代表员工目前仍在岗，而from_date则是代表员工入岗日期。所以条件de.to_date='9999-01-01’也是有必要的，因为表内也有不是现在的员工的数据。
• 另外left join和inner join都能通过，但我认为left join更对，避免漏掉某些员工数据。

12.获取所有部门中当前(dept_emp.to_date = ‘9999-01-01’)员工当前(salaries.to_date=‘9999-01-01’)薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary

select d,e,s
from (select de.dept_no d,de.emp_no e,s.salary s,rank() over(partition by de.dept_no order by salary desc) c
from dept_emp de left join salaries s
on de.emp_no=s.emp_no
and de.to_date = '9999-01-01'
and s.to_date='9999-01-01')newtable
where c=1

• 这是本人的想法，用rank函数对每个部门中所有薪水进行排名，然后选取排名为1的即为部门当前薪水最高的相关信息。其他条件在用join组合表时在on语句中限制。

13.从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。

select title,count(title) t
from titles
group by title
having t>=2

• 注意要输出的列名为t，另外WHERE后不可跟COUNT()函数，所以只能用having语句来限制条件。

14.从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
注意对于重复的emp_no进行忽略(即emp_no重复的title不计算，title对应的数目t不增加)。

select title,count(distinct emp_no) as t
from titles
group by title
having t>=2

• 相比上题多了一个条件，重复的emp_no不计算，用distinct函数解决

15.查找employees表所有emp_no为奇数，且last_name不为Mary(注意大小写)的员工信息，并按照hire_date逆序排列(题目不能使用mod函数)

select * 
from employees
where emp_no%2<>0
and last_name<>'Mary'
order by hire_date desc

• mod函数语法：mod(被除数,除数)；作用：求余数

16.统计出当前(titles.to_date=‘9999-01-01’)各个title类型对应的员工当前(salaries.to_date=‘9999-01-01’)薪水对应的平均工资。结果给出title以及平均工资avg。

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

17.获取当前（to_date=‘9999-01-01’）薪水第二多的员工的emp_no以及其对应的薪水salary

select e,s
from (select emp_no e,salary s,dense_rank() over(order by salary desc) r
from salaries
where to_date='9999-01-01')tmp
where r=2

• 用dense_rank函数相比rank函数，可以避免有多个相同最高薪水时，找不到排名为2的薪水的情况，或是排名第二的薪水有多人的情况。网上有用salary desc排序结合limit1,1的答案，但是有部分答案不能解决有多个第一名的情况以及多个第二名的情况。

select emp_no,salary
from salaries
where to_date='9999-01-01'
and salary=(select distinct salary
			from salaries
			where to_date='9999-01-01'
			order by salary desc
			limit 1,1)

• 这个代码也可以是正确答案。网上有答案未在salary=（select…)的内部添加to_date='9999-01-01’的条件。如果有员工现在的薪水和已离职员工薪水同样高且均为第二高，那么代码是正确的；要是已离职的员工薪水排在了第二名，且没有现在的员工薪水与之相同，则代码错误。所以salary对应的select内部还是要写上to_date=‘9999-01-01’，一点都马虎不得。

18.查找当前薪水(to_date=‘9999-01-01’)排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，你可以不使用order by完成吗

select e,s,l,f
from (select s.emp_no e,s.salary s,e.last_name l,e.first_name f, dense_rank() over(order by s.salary desc) r
      from salaries s join employees e
      on s.emp_no=e.emp_no
      and s.to_date='9999-01-01')tmp
where r=2

• 跟上一题思路基本一样，不强行分析了

19.查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工

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(可能有多次涨薪，没有降薪)

• ①理解题意：自入职以来的薪水涨幅，所以应该是现在薪水减去入职薪水。中间时间可能有比现在薪水更高或比入职薪水更低的情况所以不能简单用最高薪水减最低薪水。
• ②思路：做两个表，第一个表把每个人的from_date排顺序，第二个表把每个人的to_date排倒序，最后用两个表里对应序号为1的salary相减就得到每个人的涨薪情况。

select y.st-x.sf
from (select emp_no,salary sf,rank() over(partition by emp_no order by from_date) f
      from salaries)x
join (select emp_no,salary st,rank() over(partition by emp_no order by to_date desc) t
      from salaries)y
on x.emp_no=y.emp_no
and x.f=1
and y.t=1
where x.emp_no=10001

• 看了别的大佬的代码之后发现自己有点被rank()函数限制住思维了，其实用普通的排序方法结合limit就可以解。当然思路还是一样的，都是做两个表。

SELECT ( 
(SELECT salary FROM salaries WHERE emp_no = 10001 ORDER BY to_date DESC LIMIT 1) -
(SELECT salary FROM salaries WHERE emp_no = 10001 ORDER BY to_date ASC LIMIT 1)
) AS growth

• select套select这个操作，要记住……而且这样写代码，直接select不用带from！这也太酷炫了。另外这个代码更简练，耗资源更少。