牛客SQL11-20题

11、SQL209 查找employees表emp_no与last_name的员工信息

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

12、SQL210 统计出当前各个title类型对应的员工当前薪水对应的平均工资

select 
t.title,
sum(s.salary)/count(*) as  avg
from titles t
join salaries s
on t.emp_no = s.emp_no
group by t.title
order by avg

13、SQL211 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

select 
emp_no,
salary
from salaries
where salary = 
	(
	#1.求出薪水第二多的
	select 
	distinct(salary)
	from salaries s
	order by salary desc
	limit 1,1
)
order by emp_no

14、SQL212 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

#4.将员工表employess进行join，得到last_name、first_name
select 
	e.emp_no,
	tmp.salary,
	e.last_name,
	e.first_name
from employees e
join 	
(
#3.求出拥有第二大salary的员工编号
select 
	emp_no,
	salary
from salaries
where salary in
		(
		#2.求出第二大的salary
		select 
			max(salary) 
		from salaries s 
		where salary not in
				#1.先求出最大的salary
				(select 
					max(salary) salary 
				 from salaries s
				 ) 
		)
) tmp
on tmp.emp_no = e.emp_no

15、SQL213 查找所有员工的last_name和first_name以及对应的dept_name

#2.联合员工表employees去left join得到的临时表t1
select 
	e.last_name,
	e.first_name,
	t1.dept_name
from employees e
left join
		(
		#1.联合部门表departments和部门员工关系表dept_emp进行join，得到dept_name
		select
			de.emp_no,
			de.dept_no,
			d.dept_name
		from departments d
		join dept_emp de
		on d.dept_no = de.dept_no
		) t1
on e.emp_no = t1.emp_no

16、SQL215 查找在职员工自入职以来的薪水涨幅情况

#3.查出员工编号，以及两个薪水的差值
select
t1.emp_no emp_no,
(t2.new - t1.old) growth
from 
		(
		#2.求出在职员工的当前薪水情况
		select
			s.emp_no,
			s.salary new
		from salaries s
		where s.to_date = '9999-01-01'
		) t2
join
		(
		#1.求出各个员工入职的薪水情况
		select 
			s.emp_no,
			s.salary old
		from salaries s
		join employees e
		on s.from_date = e.hire_date
		) t1
on t1.emp_no = t2.emp_no
order by growth

17、SQL216 统计各个部门的工资记录数

#2.联合departments和临时表，得到dept_name
select 
	dp.dept_no,
	dp.dept_name,
    t1.sum
from departments dp
join
		(
		#1.联合dept_emp和salaries两个表，求出各个部门的工资记录数
		select
			de.dept_no,
			count(*) sum
		from dept_emp de
		join salaries s
		on de.emp_no = s.emp_no
		group by de.dept_no
		) t1
on dp.dept_no = t1.dept_no
order by dp.dept_no

18、SQL217 对所有员工的薪水按照salary降序进行1-N的排名

select
	*
from
		(
		#1.先使用开窗函数对所有员工按照salary降序排序
		select
			emp_no,
			salary,
			dense_rank() over(order by salary desc) t_rank
		from salaries s
		) t1
order by t_rank,emp_no

19、SQL218 获取所有非manager员工当前的薪水情况

select
	t1.dept_no,
	t1.emp_no,
	t1.salary
from
		(
		#1.先求出所有员工的薪水情况
		select
			de.emp_no,
			de.dept_no,
			s.salary
		from dept_emp de
		join salaries s
		on de.emp_no = s.emp_no
		) t1
		left join dept_manager dm
		on t1.emp_no = dm.emp_no
		where dm.emp_no is  null

20、SQL219 获取员工其当前的薪水比其manager当前薪水还高的相关信息

#3.然后关联两张临时表，条件是同一个部门，过滤掉员工本身是manager的，以及薪水不满足的
select
	t1.emp_no,
	t2.emp_no manager_no,
	t1.salary emp_salary,
	t2.salary manager_salary
from
		(
		#1.先求出部门所有人的薪资情况
		select 
			s.emp_no,
			s.salary,
			de.dept_no
		from dept_emp de
		join salaries s
		on de.emp_no=s.emp_no
		) t1
join
		(
		#2.再求出部门经理的薪资情况
		select
			dm.dept_no,
			s.emp_no,
			s.salary
		from dept_manager dm
		join salaries s
		on dm.emp_no=s.emp_no
		) t2
	on t1.dept_no=t2.dept_no
	where t1.emp_no != t2.emp_no and t1.salary > t2.salary