没有order by语句,rownum的结果不确定的
SQL> create table t as select level id,level||'a' name from dual connect by leve
l<=2e6;
Table created.
SQL> set lines 80 pages 50000
SQL> select bb.* from (
2 select aa.*,rownum rn from
3 (select distinct name,id from t
4 where name is not null
5 and id >=2015
6 )aa
7 where rownum<=10
8 )bb
9 where bb.rn>=1;
)bb
*
ERROR at line 8:
ORA-00911: invalid character
SQL> select bb.* from (
2 select aa.*,rownum rn from
3 (select distinct name,id from t
4 where name is not null
5 and id >=2015
6 )aa
7 where rownum<=10
8 )bb
9 where bb.rn>=1;
NAME ID RN
----------------------------------------- ---------- ----------
2015a 2015 1
2016a 2016 2
2017a 2017 3
2018a 2018 4
2019a 2019 5
2020a 2020 6
2021a 2021 7
2022a 2022 8
2023a 2023 9
2024a 2024 10
10 rows selected.
SQL> set timi on
SQL> select bb.* from (
2 select aa.*,rownum rn from
3 (select distinct name,id from t
4 where name is not null
5 and id >=2015 order by name
6 )aa
7 where rownum<=10
8 )bb
9 where bb.rn>=1;
NAME ID RN
----------------------------------------- ---------- ----------
1000000a 1000000 1
1000001a 1000001 2
1000002a 1000002 3
1000003a 1000003 4
1000004a 1000004 5
1000005a 1000005 6
1000006a 1000006 7
1000007a 1000007 8
1000008a 1000008 9
1000009a 1000009 10
10 rows selected.
Elapsed: 00:00:00.56