mysql 分组取前窗口前N的方法

1.

select * from 
(
     select depart_id,COALESCE(
(select salary from 20120823_salary t where t.depart_id=d.depart_id ORDER BY depart_id desc,salary desc,employee_id asc limit 2, 1)  ,0 ) threshold
     
from  (select DISTINCT depart_id from 20120823_salary) d
) t ,20120823_salary s
where s.depart_id=t.depart_id
and s.salary>=t.threshold

2. 

select t.employee_id,depart_id,salary
from (select @rank=1,@depart_id=null) vars
STRAIGHT_JOIN
(
select IF(@depart_id=t.depart_id ,@rank:=@rank+1, CONCAT(@depart_id:=t.depart_id,@rank:=1)) crap,
t.*,@rank rank
from 20120823_salary t
order by depart_id desc ,salary desc) t
where t.rank<=3;

3 

select * from 20120823_salary t
where (select COUNT(*)
from 20120823_salary t2
where t2.salary>t.salary
and t2.depart_id=t.depart_id
)<3;