查询部门销售代表工资最高的前1名
DEPTNO ENAME SAL DR
10 KING 5000 1
10 CLARK 2450 2
10 MILLER 1300 3
20 SCOTT 3000 1
20 FORD 3000 1
20 JONES 2975 2
20 ADAMS 1100 3
30 BLAKE 2850 1
30 ALLEN 1600 2
30 TURNER 1500 3
[解决办法]
--方法一
Select * From 表 A
WHERE SAL = (Select Max(SAL) From 表 Where DEPTNO = A.DEPTNO)
ORDER BY DEPTNO
--方法二
Select * From 表 A
WHERE Not Exists (Select SAL From 表 Where DEPTNO = A.DEPTNO And SAL > A.SAL)
ORDER BY DEPTNO
--方法三
Select A.* From 表 A
Inner Join
(Select Max(SAL) As SAL, DEPTNO From 表 Group By DEPTNO) B
On A.SAL = B.SAL And A.DEPTNO = B.DEPTNO
ORDER BY A.DEPTNO
[解决办法]
select DEPTNO,max(SAL) as sal from tablename
where deptno in (select distinct deptno from tablename)
order by deptno desc 看是不是你所要的.
我的异常网推荐解决方案:软件开发者薪资,http://www.myexception.cn/other/1391128.html