建表和插入数据语句:create table t
as
select object_name ename,
mod(object_id,50) deptno,
object_id sal
from all_objects
where rownum <= 1000用相关子查询查出每个deptno里sal最高或者最低的3个人。select *
from t t1
where t1.sal in (select sal
from (select distinct sal, deptno
from t b
where b.deptno = t1.deptno
order by b.deptno, b.sal desc) a
where rownum <= 3)
as
select object_name ename,
mod(object_id,50) deptno,
object_id sal
from all_objects
where rownum <= 1000用相关子查询查出每个deptno里sal最高或者最低的3个人。select *
from t t1
where t1.sal in (select sal
from (select distinct sal, deptno
from t b
where b.deptno = t1.deptno
order by b.deptno, b.sal desc) a
where rownum <= 3)
解决方案 »
- 关于union all和order by 同用的问题
- 机器上装有MSsql2005,我现在装Oracle9i可就是装不上,请求援助
- 一台机子两个实例问题,另外一个实例启动不了报ORA-12505错误--急!
- 请问在哪里写存储过程?在哪里执行?请给一个最简单的例子,不用带参数!
- 【DBLINK】如何获知哪些程序或者数据库在使用指定的dblink?
- 急助:朋友的一份Oracle DBA的Take Home Exam,全英文,谁能帮忙回答?
- 如何知道ASCII码代表的字符
- 一个对大家再也简单不过的问题了!!
- 如何实现oracle与access的联接
- 由于表中字段很多,我想select除了某一列的所有剩余的列。是否一定要一一列出所有字段???类似select * 有吗?
- 我要向oracle插入大量数据,平均每天是1千万条,请问各位专家高手如何提高插入速度和性能
- 16进制的String转成10进制 (高分)
---查最高
SELECT * FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RN,T.* FROM T)
WHERE RN<=3
--查最低
SELECT * FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL ) RN,T.* FROM T)
WHERE RN<=3
select *
from t t1
where t1.sal in (select sal
from (select distinct sal, deptno
from t b
where b.deptno = t1.deptno
order by b.deptno, b.sal desc) a
where rownum <= 3)
写错了当然不行~~~
你里面的ROWNUM<=3只取3条记录,而不是每个部门都取最高的3条记录。
这种情况用分析函数是最方便和省力的。
from t t1
where t1.sal in (select sal
from (select distinct sal, deptno
from t b
order by b.deptno, b.sal desc) a
where rownum <= 3 and b.deptno = t1.deptno ) 确可以得出正确的结果,请问这是什么原因?
http://topic.csdn.net/u/20081002/00/f8d90ba2-e2bb-412a-a0c5-1b6d518fc22a.html
http://topic.csdn.net/u/20081002/00/f8d90ba2-e2bb-412a-a0c5-1b6d518fc22a.html
不过里面没有和我相关的问题。谢谢eviler
不过select *
from t t1
where t1.sal in (select sal
from (select distinct sal, deptno
from t b
where b.deptno = t1.deptno
order by b.deptno, b.sal desc) a
where rownum <= 3 and t1.deptno=t1.deptno)也不对,而且据说是支持16层。