我的一个表 t1 :
字段: id(varchar型) ord(number型)
记录: 01 1
02 3
0201 3
0202 2
0203 1
03 4
04 2
等等,想得到以下结果顺序:
id ord
01 1
04 2
02 3
0201 3
0202 2
0203 1
03 4
规则是:首先按照id字段的长短排序(同样长短的按ord),如果id字段有子字段记录比如02下有0201、0202、0203,则子字段必须紧接着父字段段记录。
不知道我表达清楚了没有?
字段: id(varchar型) ord(number型)
记录: 01 1
02 3
0201 3
0202 2
0203 1
03 4
04 2
等等,想得到以下结果顺序:
id ord
01 1
04 2
02 3
0201 3
0202 2
0203 1
03 4
规则是:首先按照id字段的长短排序(同样长短的按ord),如果id字段有子字段记录比如02下有0201、0202、0203,则子字段必须紧接着父字段段记录。
不知道我表达清楚了没有?
select id,ord
from t1
order by length(trim(id)),ord,id
,y
,min(y) over(partition by substr(id, 1, 2)) x
from (
select id, ord, row_number() over (order by length(id), ord) y
from t1 order by 3
)
order by x
select id, ord
,y
,min(y) over(partition by substr(id, 1, 2)) x
from (
select id, ord, row_number() over (order by length(id), ord) y
from t1
)
order by x
select id,ord from t1
order by substr(id,1,2),length(id),ord;
,y
,min(y) over(partition by substr(id, 1, 2)) x
from (
select id, ord, row_number() over (order by length(id),id, ord) y
from t1 order by 3
)
order by x