假设表位A,字段包括员工号eno,部门号dno,工资sal,请问如何查询每个部门工资最大的员工,谢谢
解决方案 »
- 这个语句在oracle 中如何修改才能使用
- 有关Oracle的几个简单问题。困惑我好几天了都没有解决。。
- 请问一张表的极限是多少?
- oracle 10g的客户端如何连接到oracle 8的服务器
- 问树形结构模型数据库的设计问题。要求提取数据时效率高,而且数据库的结构在各种大型数据库通用,维护方便!
- 关于查询重复记录的问题?
- sql语句问题
- 如何实现数据的实时处理,使用数据库!!!
- oralce9i 装不上去 ?? 急!!!
- 如何将如下格式的时间存到Oracle的Date型字段,并原样取出?(Wed Aug 14 09:36:31 CST 2002)
- 求一算法!!!
- 简单 但 奇怪 的问题 rownum
where (sal,dno) in
(
select max(sal),dno from A)
(SELECT MAX(sal) MAXSAL,dno from A GROUP BY dno )B
WHERE A.sal = B.MAXSAL AND A.dno = B.dno
select max(sal) eno from a group by dno )
这样写不知道对不对
(SELECT MAX(sal) MAXSAL,dno from A GROUP BY dno )B
WHERE A.sal = B.MAXSAL AND A.dno = B.dno
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/functions129.htm#i1269223create table a(eno int, dno int, sal int);insert into a values(1,10,4000);
insert into a values(2,10,3400);
insert into a values(3,20,6000);
insert into a values(4,20,4000);
insert into a values(5,30,8000);
insert into a values(6,30,9000);
insert into a values(7,30,9000);
insert into a values(8,30,8500);
commit;select eno, dno, sal from
(
select eno, dno, sal, rank() over(partition by dno order by sal desc) rn
from a
)
where rn=1;
7楼有点问题,应该使用row_number()而不是rank()
SQL> select eno, dno, sal from
2 (
3 select eno, dno, sal, row_number() over(partition by dno order by sal desc) rn
4 from a
5 )
6 where rn=1;
ENO DNO SAL
--------------------------------------- --------------------------------------- ---------------------------------------
1 10 4000
3 20 6000
6 30 9000
SQL> SELECT * FROM EMP ORDER BY DEPTNO,SAL DESC;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7839 KING PRESIDENT 1981-11-17 5000.00 10
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7900 JAMES CLERK 7698 1981-12-3 950.00 3014 rows selectedSQL>
SQL> SELECT *
2 FROM EMP T1
3 WHERE NOT EXISTS (SELECT 1
4 FROM EMP T2
5 WHERE T1.DEPTNO = T2.DEPTNO
6 AND T1.SAL < T2.SAL);EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7839 KING PRESIDENT 1981-11-17 5000.00 10
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
确实,仔细想了下确实是应该用rank(),之前考虑错了