sql-server - 根据指向同一表的字段对查询进行排序

Question

我有一个名为“Sentence”的表，其中包含以下字段:

ID         <--- OK
NextID     <--- FK To ID
Text

如果我有以下记录:

*ID*            *NextID*          *Text*
1               12                The quick
3               40                jumps over
5               null              lazy dog.
12              3                 brown fox
40              5                 the

如果我知道序列的开头是 ID = 1 的记录，有没有办法根据 NextID 的序列对查询进行排序。与上面的例子一样，预期的结果应该是……

The quick
brown fox
jumps over
the
lazy dog.

我正在寻找 T-SQL 语句或以某种方式使用 Linq 执行此操作。提前致谢!

Answer 1

试试这个:

declare @YourTable table (RowID int primary key, NextID int, TextValue varchar(50))

INSERT INTO @YourTable VALUES (1 , 12  ,'The quick')
INSERT INTO @YourTable VALUES (3 , 40  ,'jumps over')
INSERT INTO @YourTable VALUES (5 , null,'lazy dog.')
INSERT INTO @YourTable VALUES (12, 3   ,'brown fox')
INSERT INTO @YourTable VALUES (40, 5   ,'the')

;with cteview as (
SELECT * FROM @YourTable WHERE RowID=1
UNION ALL
SELECT y.* FROM @YourTable y
    INNER JOIN cteview   c ON y.RowID=c.NextID
) 
select * from cteview
OPTION (MAXRECURSION 9999) --go beyond default 100 levels of recursion to 9999 levels

输出:

RowID       NextID      TextValue
----------- ----------- --------------------------------------------------
1           12          The quick
12          3           brown fox
3           40          jumps over
40          5           the
5           NULL        lazy dog.

(5 row(s) affected)