SQL TOP N 分组统计

 

--分组统计

select a.emp_id,b.emp_name,a.dept_no,a.bb_no,a.xh,a.idno,a.emp_no,a.beg_date,a.over_date,a.basesal
from HRSys.dbo.EmpbaseSal a left join HRSys.dbo.Employee b on a.emp_id=b.emp_id
where  basesal>4000
order by a.emp_id,basesal

--第一种TOP N 分组统计
select a.emp_id,b.emp_name,a.dept_no,a.bb_no,a.xh,a.idno,a.emp_no,a.beg_date,a.over_date,a.basesal
from HRSys.dbo.EmpbaseSal a left join HRSys.dbo.Employee b on a.emp_id=b.emp_id
where (select   count(1)   from   HRSys.dbo.EmpbaseSal
       where   emp_id=a.emp_id   and   basesal>=a.basesal)<=3
 and basesal>4000
order by a.emp_id,basesal desc

--第二种 TOP　Ｎ　分组统计
select a.emp_id,b.emp_name,a.dept_no,a.bb_no,a.xh,a.idno,a.emp_no,a.beg_date,a.over_date,a.basesal
from HRSys.dbo.EmpbaseSal a left join HRSys.dbo.Employee b on a.emp_id=b.emp_id
where basesal in (select  top  3 basesal  from   HRSys.dbo.EmpbaseSal
       where   emp_id=a.emp_id  order by basesal desc )
 and basesal>4000
order by a.emp_id,basesal   

--分组统计

select a.emp_id,b.emp_name,a.dept_no,a.bb_no,a.xh,a.idno,a.emp_no,a.beg_date,a.over_date,a.basesal
from HRSys.dbo.EmpbaseSal a left join HRSys.dbo.Employee b on a.emp_id=b.emp_id
where  basesal>4000
order by a.emp_id,basesal

--第一种TOP N 分组统计
select a.emp_id,b.emp_name,a.dept_no,a.bb_no,a.xh,a.idno,a.emp_no,a.beg_date,a.over_date,a.basesal
from HRSys.dbo.EmpbaseSal a left join HRSys.dbo.Employee b on a.emp_id=b.emp_id
where (select   count(1)   from   HRSys.dbo.EmpbaseSal
       where   emp_id=a.emp_id   and   basesal>=a.basesal)<=3
 and basesal>4000
order by a.emp_id,basesal desc

--第二种 TOP　Ｎ　分组统计
select a.emp_id,b.emp_name,a.dept_no,a.bb_no,a.xh,a.idno,a.emp_no,a.beg_date,a.over_date,a.basesal
from HRSys.dbo.EmpbaseSal a left join HRSys.dbo.Employee b on a.emp_id=b.emp_id
where basesal in (select  top  3 basesal  from   HRSys.dbo.EmpbaseSal
       where   emp_id=a.emp_id  order by basesal desc )
 and basesal>4000
order by a.emp_id,basesal