如何找出一个公司各个部门中工资最高的两个人?
--TB_Dept(部门表) 有Dept_id,Dept_name
--TB_Employee(雇员表) 有 employee_id,employee_name,Dept_id
--TB_pay(工资表) employee_id,pay(工资)
--TB_Dept(部门表) 有Dept_id,Dept_name
--TB_Employee(雇员表) 有 employee_id,employee_name,Dept_id
--TB_pay(工资表) employee_id,pay(工资)
from TB_Dept A inner join B on A.Dept_id = B.Dept_id
inner join C on B.employee_id = D.employee_id
order by C.pay没测验..
inner join TB_pay on e.employee_id=p.employee_id order by p.pay desc
group by e.Dept_id 应该没问题的
所以不会只列出2个人,
楼上大多用top 2,肯定不行!!
inner join TB_pay on e.employee_id=p.employee_id order by p.pay desc
group by e.Dept_id
这样就可以了
select a.Dept_id,a.employee_id,max(b.pay) from TB_Employee a
left join TB_pay b on a.employee_id=b.employee_id
left join TB_Dept c on c.Dept_id = a.Dept_id
group by a.Dept_id,a.employee_id
union
select a.Dept_id,a.employee_id,max(b.pay) from TB_Employee a
left join TB_pay b group by a.Dept_id,a.employee_id
left join TB_Dept c on c.Dept_id = a.Dept_id
left join (select a.Dept_id,a.employee_id,max(b.pay) from TB_Employee a
left join TB_pay b on a.employee_id=b.employee_id
group by a.Dept_id,a.employee_id) d
on a.Dept_id<>d.Dept_id,a.employee_id<>d.employee_id
group by a.Dept_id,a.employee_id
--TB_Employee(雇员表) 有 employee_id,employee_name,Dept_id
--TB_pay(工资表) employee_id,pay(工资)
select e.employee_id, e.employee_name, e.Dept_id, p.pay
from TB_Employee e inner join TB_pay p on e.employee_id=p.employee_id
where e.employee_id in (select top 2 employee_id from TB_Employee where Dept_id=e.Dept_id)
--TB_Employee(雇员表) 有 employee_id,employee_name,Dept_id
--TB_pay(工资表) employee_id,pay(工资)
select a.employee_id,t.employee_name,t.Dept_name,t.dept_id,a.pay into #t1 from TB_pay a left join
(select * from TB_Employee b left join TB_Dept c on b.Dept_id=c.Dept_id )t
on a.employee_id=t.employee_idselect * from #t1 a where employee_id =
( select top 2 employee_id where dept_id=a.dept_id order by pay desc )
--TB_Employee(雇员表) 有 employee_id,employee_name,Dept_id
--TB_pay(工资表) employee_id,pay(工资)
select a.employee_id,t.employee_name,t.Dept_name,t.dept_id,a.pay into #t1 from TB_pay a left join
(select * from TB_Employee b left join TB_Dept c on b.Dept_id=c.Dept_id )t
on a.employee_id=t.employee_idselect * from #t1 a where employee_id =
( select top 2 employee_id from #t1 where dept_id=a.dept_id order by pay desc )