select * from 员工表 a where EMPNO in (select top 3 EMPNO from 员工表 where DEPTID = a.DEPTID order by SALARY desc)
select * from 员工表 as t where (select count(*) from 员工表 where DEPTID = t.DEPTID and SALARY < t.SALARY) < 3
select top 3 EMPNO from 员工表 where DEPTID = a.DEPTID order by SALARY desc
select top 3 EMPNO,DEPTID,SALARY from 员工表 order by SALARY desc
select * from 员工表 as t where EMPNO in (select top 3 EMPNO from 员工表 where DEPTID = t.DEPTID order by SALARY desc )
select a.* from 员工表 a,(select distinct DEPTID from 员工表) as t where a.DEPTID = t.DEPTID and EMPNO in (select top 3 EMPNO from 员工表 where DEPTID = t.DEPTID order by SALARY desc ) order by a.DEPTID , a.SALARY desc
select * from table as t where (select count(*) from table where DEPTID = t.DEPTID and SALARY > t.SALARY) < 3 --上句是找出同班中比自己分数高的不超过3个的记录 order by DEPTID,SALARY desc
如果是Oracle用RANK函数SELECT * FROM (SELECT a.*,OrdNum=RANK() OVER (PARTITION BY DEPTID ORDER BY SALARY) FROM TABLE) WHERE OrdNum<=3得到的OrdNum就是按部门的薪水排名, 好象Sql 2005也支持RANK,只是不知道写法是否一样,MS可是个抄袭专家哟
上面是按升序排的,要降序排加个DESC就好了
如果部门少,直接TOP排序就行了.
select e.* from empno e where e.id in ( select id from empno group by deptno order by salary desc limit 3 ) 帮我看一下这样行不行
a.DEPTID = t.DEPTID and
EMPNO in (select top 3 EMPNO from 员工表 where DEPTID = t.DEPTID order by SALARY desc )
order by a.DEPTID , a.SALARY desc
(select count(*) from table where DEPTID = t.DEPTID and SALARY > t.SALARY) < 3
--上句是找出同班中比自己分数高的不超过3个的记录
order by DEPTID,SALARY desc
(SELECT a.*,OrdNum=RANK() OVER (PARTITION BY DEPTID ORDER BY SALARY) FROM TABLE)
WHERE OrdNum<=3得到的OrdNum就是按部门的薪水排名,
好象Sql 2005也支持RANK,只是不知道写法是否一样,MS可是个抄袭专家哟
select id from empno group by deptno order by salary desc limit 3 )
帮我看一下这样行不行