该答案假定您使用的是SQL Server 2008或更高版本。
使用递归CTE并构建按顺序使用的ID字符串，作为层次结构ID。
with C as( select id, title, parentid, '/'+cast(id as varchar(max))+'/' as sort, 1 as lvl from YourTable where parentid = 0 union all select T.id, T.title, T.parentid, C.sort + cast(T.id as varchar(10)) + '/', C.lvl + 1 from YourTable as T inner join C on T.parentid = C.id)select id, title, parentid, lvl, sortfrom Corder by cast(sort as hierarchyid)
SQL小提琴
