Ordering a Linked List-Structure in a SQL or LINQ Query?
I have a database that essentially looks like this:
id uniqueidentifier NOT NULL
data nvarchar
nextid uniqueidentifier NULL
This is a Linked List, as each nextid links to an id in that table, except for the 开发者_JAVA百科last one, here nextid is NULL. I do know the id of the first node.
I want to SELECT them all in the correct order, given a start id.
Is there a way this can be done in T-SQL (Edit: SQL 2008) or LINQ?
I know I can write code to do it manually in C#, just wondering if I can already query in that order?
Not sure of [SortOrder] makes any difference as I don't have enough data to test it. It allows you to sort in both directions.
with cteList as
(
select id, data, nextid, 1 as [SortOrder]
from #TableTemp
where id = 'E8ADAA52-54F8-4FE3-BE59-9852E52B33F5' --id of the 1st item
union all
select #TableTemp.id, #TableTemp.data, #TableTemp.nextid, (cteList.[SortOrder] + 1) as [SortOrder]
from #TableTemp
join cteList on #TableTemp.id = cteList.nextid
)
select * from cteList
order by [SortOrder] asc
What version of SQL Server? If it's 2005 or newer you can use a recursive CTE do it.
with linked_list as (
select
id, data, nextid
from
table
where
id = @head
union all
select
t.id, t.data, t.nextid
from
table t
join linked_list ll on t.id = ll.nextid
)
select
*
from
linked_list
精彩评论