SQL递归查询只返回最后一行 [英] SQL Recursive Query only return the last row

问题描述

我正在尝试使用简单的 SQL Server 2008 递归查询.

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

我有一张表，有 id 和 parentID:

ID fParent fName2 空根3 2 Drug_Error4 2 事件5 4 20076 4 20097 5 2007-18 7 2008-2

使用以下查询

 with recury as (选择fs1.ID ,fs1.FParent,fs1.FName从 FoldersStructure 作为 fs1其中 fs1.FParent =null联合所有选择 fs2.id,fs2.FParent,fs2.FName从 FoldersStructure 作为 fs2在 fs2.FParent= r.ID 上将内部连接作为 r 递归)选择 ID、FParent、FName从回收其中 ID=8

我希望得到:

2 空根4 2 事件5 4 20077 5 2007-18 7 2007-2

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

解决方案

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

WITH recury (Id, ParentId, Name, Level) AS(选择 fs1.Id ,fs1.ParentId,fs1.Name, CONVERT(int, 0)FROM FoldersStructure AS fs1其中 fs1.Id = 8联合所有选择 fs2.Id,fs2.ParentId,fs2.Name, 级别 - 1FROM FoldersStructure AS fs2JOIN recury AS r ON fs2.Id = r.ParentId)选择 ID、ParentId、名称、级别FROM 回收按级别排序；

如果父行的 ID 不是按数字顺序排列，此代码将起作用.如果您的父行始终保证按数字顺序排列，您可以省略 CTE 中引入的 Level 列，并根据 bummi 的回答对 Id 列进行排序.>

SQL 小提琴示例:http://sqlfiddle.com/#!3/2af0c/4

I am trying to get the simple SQL Server 2008 Recursive Query to work.

Following these examples: http://msdn.microsoft.com/en-us/library/ms186243.aspx and SQL Server recursive query

I have a table, with id and 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 the following query

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

I was hoping to get:

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

But I only get the last one. thanks in advance.

解决方案

Remove the WHERE clause from the statement because it is limiting the resultset to rows where Id = 8. Based on the first comment below, I now understand your requirement! To use 8 as your starting point and to retrieve all parent rows:

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;

This code will work if the Ids of the parent rows are not in numeric order. If your parent rows always guaranteed to be in numeric order, you can omit the Level column introduced in the CTE and sort on the Id column instead as per bummi's answer.

SQL fiddle example: http://sqlfiddle.com/#!3/2af0c/4

这篇关于SQL递归查询只返回最后一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！