--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[previous] int)
Insert #T
select 1,0 union all
select 2,1 union all
select 3,5 union all
select 4,2 union all
select 5,4 union all
select 6,3 union all
select 7,6
Go
--测试数据结束
;WITH cte AS (
SELECT * ,
1 AS ordernum
FROM #T
WHERE previous = 0
UNION ALL
SELECT #T.* ,
cte.ordernum + 1
FROM #T
JOIN cte ON cte.id = #T.previous
)
SELECT id ,
cte.previous
FROM cte
ORDER BY cte.ordernum;
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[previous] int)
Insert #T
select 1,0 union all
select 2,1 union all
select 3,5 union all
select 4,2 union all
select 5,4 union all
select 6,3 union all
select 7,6
Go
--测试数据结束
Select * from #T ORDER BY previous