1:找出公司里收入最高的前三名员工:SQL> select rownum, last_name, salary
2 from (select last_name, salary
3 from s_emp
4 order by salary desc)
5 where rownum<=3;ROWNUM LAST_NAME SALARY
---------- ------------------------- ----------
1 Velasquez 4750
2 Ropeburn 2945
3 Nguyen 2897.5注意:请大家分析一下一下语句为什么不对:SQL> select rownum, last_name, salary
2 from s_emp
3 where rownum<=3
4 order by salary desc;ROWNUM LAST_NAME SALARY
---------- ------------------------- ----------
1 Velasquez 4750
3 Nagayama 2660
2 Ngao 2000
2 from (select last_name, salary
3 from s_emp
4 order by salary desc)
5 where rownum<=3;ROWNUM LAST_NAME SALARY
---------- ------------------------- ----------
1 Velasquez 4750
2 Ropeburn 2945
3 Nguyen 2897.5注意:请大家分析一下一下语句为什么不对:SQL> select rownum, last_name, salary
2 from s_emp
3 where rownum<=3
4 order by salary desc;ROWNUM LAST_NAME SALARY
---------- ------------------------- ----------
1 Velasquez 4750
3 Nagayama 2660
2 Ngao 2000
解决方案 »
- 请问怎么开启闪回?
- submit 实现搜索功能
- 问大家个关于日志的问题
- 如何修改
- 菜鸟问题:创建表时字段名可以是变量吗?如果是,怎么做?
- 手动创建oracle数据库总是failure in processing system parameters!我快要崩溃了!新手求解答
- 麻烦给提供个oracle 下载地址 急用
- 使用pl/sql dev的问题,顺便散分
- 高分求救!LONG数据类型,如何知道有没有包含某字符???
- 24小时在线求助!!!!,请问怎样在p41.7的机器上,在操作系统是redhat8.o下安装oracle9i
- 求SQL语句问题,想了2天 没想出 求教
- ado连接oracle报ORA-00604错
2 from s_emp
3 where rownum <=3 取出来的是前三条记录,并一定是收入最高的。
确切的说,如果salary 是主键的话,那两个语句都一样,第二个效率高
如果不是主键的话,那第二个语句的结果就是错的
2 from s_emp
3 where rownum <=3
4 order by salary desc;
------------------------------
这一句,是先从s_emp抽出前面三条,然后再进行排序
http://topic.csdn.net/u/20080827/17/5962849a-6504-4b4b-8d71-fcf9bf7f9664.html
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
BM NUMBER(4) Y
YP VARCHAR2(10) Y
SQL> insert into test_a values(1000,'a');
1 row inserted
SQL> insert into test_a values(2000,'a');
1 row inserted
SQL> insert into test_a values(3000,'a');
1 row inserted
SQL> insert into test_a values(4000,'a');
1 row inserted
SQL> insert into test_a values(5000,'a');
1 row inserted
SQL> insert into test_a values(6000,'a');
1 row insertedSQL> commit;
Commit completeSQL> select * from test_a;
BM YP
----- ----------
1000 a
2000 a
3000 a
4000 a
5000 a
6000 a
6 rows selectedSQL> select bm,yp from test_a where rownum<=3 order by bm desc;
BM YP
----- ----------
3000 a
2000 a
1000 aSQL> alter table test_a add primary key(bm);
Table altered
SQL> select bm,yp from test_a where rownum<=3 order by bm desc;
BM YP
----- ----------
6000 a
5000 a
4000 a