表格Table, 字段LevelID(字符型),Re(字符型)
1.0, aaa
1.1, bbb
1.1.1,ccc
1.10,ddd
1.2,eee
1.2.1,fff
1.20,ggg
1.3,iii直接Order by LevelID,无法实现我下面的效果1.0, aaa
1.1, bbb
1.1.1,ccc
1.2,eee
1.2.1,fff
1.3,iii
1.10,ddd
1.20,ggg
按照.分层,每一层,我都想按照数值排序Sql
1.0, aaa
1.1, bbb
1.1.1,ccc
1.10,ddd
1.2,eee
1.2.1,fff
1.20,ggg
1.3,iii直接Order by LevelID,无法实现我下面的效果1.0, aaa
1.1, bbb
1.1.1,ccc
1.2,eee
1.2.1,fff
1.3,iii
1.10,ddd
1.20,ggg
按照.分层,每一层,我都想按照数值排序Sql
SQL最好在SQL版问。
字段LevelID(字符型),Re(字符型),sort_id(排序顺序,数字型)
1.0, aaa,0
1.1, bbb,1
1.1.1,ccc,1.1
1.10,ddd,10
1.2,eee,2
1.2.1,fff,2.1
1.20,ggg,20
1.3,iii,3
select LevelID,Re from Table order by sort_id
应该就是你想要的结果
SUBSTRING(
id,
CHARINDEX('.',id,0)+1,
CASE CHARINDEX('.',id,CHARINDEX('.',id,0)+1) WHEN 0 THEN LEN(ID)-CHARINDEX('.',id,0) ELSE CHARINDEX('.',id,CHARINDEX('.',id,0)+1)-CHARINDEX('.',id,0)-1 END
) as l2,
CASE CHARINDEX('.',id,CHARINDEX('.',id,0)+1) WHEN 0 THEN LEN(ID)-CHARINDEX('.',id,0) ELSE CHARINDEX('.',id,CHARINDEX('.',id,0)+1)-CHARINDEX('.',id,0)-1 END
from ttt
ORDER BY cast(SUBSTRING(id,0,CHARINDEX('.',id,0)) as int),
cast(SUBSTRING(
id,
CHARINDEX('.',id,0)+1,
CASE CHARINDEX('.',id,CHARINDEX('.',id,0)+1) WHEN 0 THEN LEN(ID)-CHARINDEX('.',id,0) ELSE CHARINDEX('.',id,CHARINDEX('.',id,0)+1)-CHARINDEX('.',id,0)-1 END
) as int)
,id
一层一层递归