select * from ( select t1.*,dense_rank() over(order by t1.id ) paixuNO from tableName t1 ) where paixuNo<=100;
select * from users order by user_id我取出来的值 是 1 11 12 13 14 15 16 17 18 19 2 ........我想要的结果是 1 2 3 4 5 6 7.....
可以用rownumselect rownum-1 id a.* from (select * from users order by user_id) a
虽然我没见到你的表,但很明显你ID的列应该是varcahr2类型的,不是number类型的
这不是order by的问题,是你数据本身的问题SQL> --生成小于50的10个随机数,不排序 SQL> select * from ( 2 select r from ( 3 select rownum r 4 from all_objects 5 where rownum < 50) 6 order by dbms_random.value) 7 where rownum < 11 8 /
R ---------- 44 15 21 38 42 4 10 47 23 8
10 rows selected
SQL> --排序 SQL> select * from ( 2 select r from ( 3 select rownum r 4 from all_objects 5 where rownum < 50) 6 order by dbms_random.value) 7 where rownum < 11 8 order by r 9 /
R ---------- 2 5 15 16 19 29 32 34 40 49
10 rows selected
SQL>
select rownum-1 id, a.* from (select * from users order by user_id) a
这个应该可以:select aa from ( select convert(int, id) aa from table ) bb order by aa
--或者使用decode函数来手工排序,不过排序字段数据应该比较少
order by decode(col_number,1,0
2,1,
3,2,
4,3,
5,4,
6,5);
怎么就order by不出来呢?很奇怪....................
select * from (
select t1.*,dense_rank() over(order by t1.id ) paixuNO from tableName t1
) where paixuNo<=100;
1
11
12
13
14
15
16
17
18
19
2
........我想要的结果是
1
2
3
4
5
6
7.....
from (select * from users order by user_id) a
SQL> select * from (
2 select r from (
3 select rownum r
4 from all_objects
5 where rownum < 50)
6 order by dbms_random.value)
7 where rownum < 11
8 /
R
----------
44
15
21
38
42
4
10
47
23
8
10 rows selected
SQL> --排序
SQL> select * from (
2 select r from (
3 select rownum r
4 from all_objects
5 where rownum < 50)
6 order by dbms_random.value)
7 where rownum < 11
8 order by r
9 /
R
----------
2
5
15
16
19
29
32
34
40
49
10 rows selected
SQL>
from (select * from users order by user_id) a
(
select convert(int, id) aa from table
) bb
order by aa