设有关系EMP(ENO,ENAME,SALARY,DNO),其中各属性的含义依次为职工号、姓名、工资和所在部门号,以及关系DEPT(DNO,DNAME,MANAGER),其中各属性含义依次为部门 号、部门名称、部门经理的职工号。
求每个部门工资的前两位,然后怎么写?
select top 2 salary from emp
order by salary desc
求每个部门工资的前两位,然后怎么写?
select top 2 salary from emp
order by salary desc
select top 2 a.salary from emp a
where a.ENO in(select ENO from emp where a.DNO=DNO order by salary desc )
select a.salary from emp a
where a.ENO in(select top 2 ENO from emp where a.DNO=DNO order by salary desc )
--------------------------------------------------------
先把两个表关联起来,再用mugua604(熟不了的木瓜) 的方法:
select b.DNAME,a.ENAME,a.salary from emp a join DEPT b on a.DNO=b.DNO
where a.ENO in(select top 2 ENO from emp where a.DNO=DNO order by salary desc )
order by b.DNAME,a.salary desc
where t1.dno = t2.dno and 2>(select count(1) from emp a where a.dno=t1.dno and a.salary>t1.salary) order by 1,5 desc
where a.ENO in(select top 2 ENO from emp where a.DNO=DNO order by salary desc )
这个可以达到...