题解 | #获取员工其当前的薪水比其manager当前薪水还高的相关信息#

三种写法

# 3表连接
-- 按员工分组，得到title，然后计算count
select de.dept_no, d.dept_name, title, count(distinct title)
from dept_emp as de 
inner join titles t  on de.emp_no=t.emp_no
inner join departments d on de.dept_no=d.dept_no
where de.to_date='9999-01-01' and  t.to_date='9999-01-01'
group by de.emp_no -- 按员工
having count(distinct title)
order by de.dept_no


# 法2
-- 按照d.dept_no部门、title分组
select de.dept_no, d.dept_name, title, count(distinct title)
from dept_emp as de 
inner join titles t  on de.emp_no=t.emp_no
inner join departments d on de.dept_no=d.dept_no
where de.to_date='9999-01-01' and t.to_date='9999-01-01'
group by d.dept_no, title
having count(distinct title)
order by de.dept_no


# 法3 开窗函数
-- count(t.emp_no) over(parittion by ...)
select distinct d.dept_no, d.dept_name, t.title, 
count(t.emp_no)over(partition by de.emp_no, t.title )
from departments as d 
join dept_emp as de on d.dept_no = de.dept_no
join titles as t on t.emp_no = de.emp_no
where de.to_date = '9999-01-01'
and t.to_date = '9999-01-01'
order by d.dept_no;