SQL> create table a(a number,b number);表已创建。SQL> insert into a(a,b) values(1,3);已创建 1 行。SQL> insert into a(a,b) values(2,5);已创建 1 行。SQL> insert into a(a,b) values(7,8);已创建 1 行。SQL> insert into a(a,b) values(5,4);已创建 1 行。SQL> select a,b from a order by b; A B ---------- ---------- 1 3 5 4 2 5 7 8 SQL> select a from (select a,b from a order by b desc) where rownum<2; A ---------- 7
如果同一个最大值a1由多个no 对应,那么放宽rounum的限制,如: SQL> insert into a(a,b) values(6,8);已创建 1 行。 SQL> select a,b from (select a,b from a order by b desc) where rownum<3; A B ---------- ---------- 7 8 6 8SQL> select a,b from (select a,b from a order by b desc) where rownum<4; A B ---------- ---------- 7 8 6 8 2 5
select * from a , (select max(a1) as a1 from a) b where a.a1 = b.a1 这是SQL的语法,不知道oracle可行否?
select no from a where a1 =(select max(a1) from a)若是一个最大值对应多个NO 上面这样最容易理解
---------- ----------
1 3
5 4
2 5
7 8
SQL> select a from (select a,b from a order by b desc) where rownum<2; A
----------
7
SQL> insert into a(a,b) values(6,8);已创建 1 行。
SQL> select a,b from (select a,b from a order by b desc) where rownum<3; A B
---------- ----------
7 8
6 8SQL> select a,b from (select a,b from a order by b desc) where rownum<4; A B
---------- ----------
7 8
6 8
2 5
这是SQL的语法,不知道oracle可行否?