oracle排序问题

表格test数据

select * from test order by name desc

结果：

 

1-1：select * from ( select A.*,rownum rn from test A order by name asc ) where rn between 0 and 10

结果：

1-2：select * from ( select A.*,rownum rn from test A order by name desc ) where rn between 0 and 10

结果：

2-1：select A.*,rownum from test A where rownum between 0 and 10 order by name desc

结果：

 

2-2：select A.*,rownum from test A where rownum between 0 and 10 order by name asc

结果：

3-1：select * from ( select A.*,Row_Number() OVER (order by name desc) as rn from test A ) where rn between 0 and 10

结果：

3-2：select * from ( select A.*,Row_Number() OVER (order by name asc) as rn from test A ) where rn between 0 and 10

结果：

 

 结论：第三种查询语句是先排序后取数据，而前面两种是先取数据后查询，对于不是主键的字段。