SQL递归查询只返回最后一行

Question

我正在尝试让简单的SQL Server 2008递归查询正常工作。

以下示例：http://msdn.microsoft.com/en-us/library/ms186243.aspx和SQL Server递归查询

我有一张桌子，上面有id和parentID:

ID  fParent fName
2   NULL     root   
3    2       Drug_Error 
4    2       Incident   
5    4       2007   
6    4       2009   
7    5       2007-1
8    7       2008-2

带有以下查询

with recury as (
Select 
fs1.ID ,fs1.FParent,fs1.FName 
from  FoldersStructure as  fs1
where fs1.FParent =null
union all
select fs2.id,fs2.FParent,fs2.FName 
from FoldersStructure as  fs2
inner join recury as r on fs2.FParent= r.ID 
)
select ID,FParent,FName
from recury 
where ID=8

我希望得到：

2    null    root
4    2        incident
5    4        2007
7    5        2007-1
8    7        2007-2

但我只得到最后一个。提前谢谢。

Answer 1

with recury as (
Select 
fs1.ID ,fs1.FParent,fs1.FName 
from  FoldersStructure as  fs1
where fs1.ID=8
union all
select fs2.id,fs2.FParent,fs2.FName 
from FoldersStructure as  fs2
inner join recury as r on fs2.ID= r.FParent
)
select ID,FParent,FName
from recury 
order by ID 

SQL Fiddle

Answer 2

从语句中删除WHERE子句，因为它将结果集限制为Id=8的行。根据下面的第一条评论，我现在理解你的要求了！使用8作为起点并检索所有父行：

WITH recury (Id, ParentId, Name, Level) AS
(
  SELECT fs1.Id ,fs1.ParentId,fs1.Name, CONVERT(int, 0)
  FROM  FoldersStructure AS  fs1
  WHERE fs1.Id = 8
  UNION ALL
  SELECT fs2.Id,fs2.ParentId,fs2.Name, Level - 1
  FROM FoldersStructure AS  fs2
  JOIN recury AS r ON fs2.Id = r.ParentId
)
SELECT Id, ParentId, Name, Level
FROM recury 
ORDER BY  Level;

如果父行的Id不按数字顺序排列，则此代码将起作用。如果父行总是保证按数字顺序排列，则可以省略CTE中引入的Level列，并根据bummi的答案在Id列上排序。

SQL fiddle示例：http://sqlfiddle.com/#！3/2af0c/4