使用SQL2005 递归查询结合Row_Number()实现完全SQL端树排序
在实际应用中，我们经常需要用到树型结构功能，数据库结构一般如下
即用一个ParentID来标识该节点从属关系。为了最终生成一棵树，一般做法是把记录选出来，然后在程序里递归重新排好序后再呈现出来，但是如果有大量数据，就带来了性能开销问题。那么能不能直接在数据库利用SQL语句排好树后再输出呢？
SQL2005 有个递归查询功能也就是WITH .. AS 语句。对上面这样的表格使用递归查询，可以查询得到某树支下（包括根）的所有节点记录。类似语句如下：
with RelClass
as
(
select * from CMS_Site_Class where ClassID = 1
union all
select csc.* from CMS_Site_Class as csc inner join RelClass as rc on csc.ClassID_Parent = rc.ClassID )
SELECT * from RelClass
将得到ClassID为1的根节点下的所有记录：
但是这个记录集显然没有经过树排序，这时还需要程序里进一步处理才能输出到客户端。在这里我介绍一种WITH 结合 Row_Number() 实现SQL端排序的方法。
先来看看最终的代码：
Code
-- =============================================
-- Author: <kingimg>
-- Create date: <2009-2-5>
-- Description: <生成已排序的树>
-- =============================================
Create PROCEDURE [dbo].[pCMS_Site_Class__GetList]
@ClassID int
AS
BEGIN
with RelClass
as
(
select *,0 as Level,cast('0' as nvarchar(max)) as treepath from CMS_Site_Class where ClassID = @ClassID
union all
select csc.*,rc.[Level] + 1,rc.treepath + dbo.Lpad(Row_Number() over (order by csc.OrderID desc),8) as treepath from CMS_Site_Class as csc inner join RelClass as rc on csc.ClassID_Parent = rc.ClassID )
SELECT * from RelClass order by treepath
END
执行以上存储过程，最后就输出结果：
这棵树已经从上到下按树结构排好序了！程序里只要原样输出即可！
Lpad函数将指定Int型数据左填0，按指定位数输出。关于为什么要用nvarchar(max)的问题，因为其它固定长度时，在递归查询里的rc.treepath + dbo.Lpad(..)时会改变长度，导致查询错误，使用max长度就避免了这个问题。当然，你也可以用固定长度，相加后再convert回来。
好了，这样子我们就实现了完全SQL端生成已排序的树的目的了，完全脱离了程序处理，这个方法看起来效率还不错呢~
我在此抛砖引玉了，各位如果有可以改进的地方，请贴出来~
不错不错。以前在程序里递归，总觉得麻烦。
做这样的功能，熟悉NicPetshop的话只需要1分钟，几乎都是生成的
再附上MSDN的解說：
使用公用表表达式的递归查询
公用表表达式 (CTE) 具有一个重要的优点，那就是能够引用其自身，从而创建递归 CTE。递归 CTE 是一个重复执行初始 CTE 以返回数据子集直到获取完整结果集的公用表表达式。
在 SQL Server 2005 中，当某个查询引用递归 CTE 时，它即被称为“递归查询”。递归查询通常用于返回分层数据，例如：显示某个组织图中的雇员或物料清单方案（其中父级产品有一个或多个组件，而那些组件可能还有子组件，或者是其他父级产品的组件）中的数据。
递归 CTE 可以极大地简化在 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句中运行递归查询所需的代码。在 SQL Server 的早期版本中，递归查询通常需要使用临时表、游标和逻辑来控制递归步骤流。有关公用表表达式的详细信息，请参阅使用公用表表达式。
递归 CTE 的结构
Transact-SQL 中的递归 CTE 的结构与其他编程语言中的递归例程相似。尽管其他语言中的递归例程返回标量值，但递归 CTE 可以返回多行。
递归 CTE 由下列三个元素组成：
- 例程的调用。
递归 CTE 的第一个调用包括一个或多个由 UNION ALL、UNION、EXCEPT 或 INTERSECT 运算符联接的 CTE_query_definitions。由于这些查询定义形成了 CTE 结构的基准结果集，所以它们被称为“定位点成员”。
CTE_query_definitions 被视为定位点成员，除非它们引用了 CTE 本身。所有定位点成员查询定义必须放置在第一个递归成员定义之前，而且必须使用 UNION ALL 运算符联接最后一个定位点成员和第一个递归成员。 - 例程的递归调用。
递归调用包括一个或多个由引用 CTE 本身的 UNION ALL 运算符联接的 CTE_query_definitions。这些查询定义被称为“递归成员”。 - 终止检查。
终止检查是隐式的；当上一个调用中未返回行时，递归将停止。
注意： |
如果递归 CTE 组合不正确，可能会导致无限循环。例如，如果递归成员查询定义对父列和子列返回相同的值，则会造成无限循环。在测试递归查询的结果时，可以通过在 INSERT、UPDATE、DELETE 或 SELECT 语句的 OPTION 子句中使用 MAXRECURSION 提示和 0 到 32,767 之间的值，来限制特定语句允许的递归级数。有关详细信息，请参阅查询提示 (Transact-SQL) 和 WITH common_table_expression (Transact-SQL)。 |
伪代码和语义
递归 CTE 结构必须至少包含一个定位点成员和一个递归成员。以下伪代码显示了包含一个定位点成员和一个递归成员的简单递归 CTE 的组件。
WITH cte_name ( column_name [,...n] )
AS
(
CTE_query_definition –- Anchor member is defined.
UNION ALL
CTE_query_definition –- Recursive member is defined referencing cte_name.
)
-- Statement using the CTE
SELECT *
FROM cte_name递归执行的语义如下：
- 将 CTE 表达式拆分为定位点成员和递归成员。
- 运行定位点成员，创建第一个调用或基准结果集 (T0)。
- 运行递归成员，将 Ti 作为输入，将 Ti+1 作为输出。
- 重复步骤 3，直到返回空集。
- 返回结果集。这是对 T0 到 Tn 执行 UNION ALL 的结果。
示例
以下示例通过返回 Adventure Works Cycles 公司的雇员的分层列表（从最高级雇员开始）显示递归 CTE 结构的语义。执行 CTE 的语句将结果集限制到研发组中的雇员。示例后面是代码执行的演练。
USE AdventureWorks;
GO
WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
AS
(
-- Anchor member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
0 AS Level
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
WHERE ManagerID IS NULL
UNION ALL
-- Recursive member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
Level + 1
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, Level
FROM DirectReports
INNER JOIN HumanResources.Department AS dp
ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Research and Development' OR Level = 0;
GO示例代码演练
- 递归 CTE
DirectReports定义了一个定位点成员和一个递归成员。 - 定位点成员返回基准结果集 T0。这就是公司中的最高级雇员，即不向经理报告的雇员。
以下是定位点成员返回的结果集：
ManagerID EmployeeID Title Level
--------- ---------- --------------------------------------- ------
NULL 109 Chief Executive Officer 0- 递归成员返回定位点成员结果集中的雇员的直接下属。这是通过在
Employee表和DirectReportsCTE 之间执行联接操作获得的。正是此次对 CTE 自身的引用建立了递归调用。利用 CTEDirectReports中的雇员作为输入 (Ti)，联接(Employee.ManagerID = DirectReports.EmployeeID) 返回经理为 (Ti) 的雇员作为输出 (Ti+1)。这样，递归成员的第一次迭代返回了以下结果集：
ManagerID EmployeeID Title Level
--------- ---------- --------------------------------------- ------
109 12 Vice President of Engineering 1- 重复激活递归成员。递归成员的第二次迭代使用步骤 3 中的单行结果集（包含
EmployeeID12）作为输入值，并返回以下结果集：
ManagerID EmployeeID Title Level
--------- ---------- --------------------------------------- ------
12 3 Engineering Manager 2- 递归成员的第三次迭代使用上面的单行结果集（包含
EmployeeID3)）作为输入值，并返回以下结果集：
ManagerID EmployeeID Title Level
--------- ---------- --------------------------------------- ------
3 4 Senior Tool Designer 3
3 9 Design Engineer 3
3 11 Design Engineer 3
3 158 Research and Development Manager 3
3 263 Senior Tool Designer 3
3 267 Senior Design Engineer 3
3 270 Design Engineer 3- 递归成员的第四次迭代使用
EmployeeID值4、9、11、158、263、267和270的上一个行集作为输入值。
重复此过程，直到递归成员返回一个空结果集。 - 正在运行的查询返回的最终结果集是定位点成员和递归成员生成的所有结果集的并集。
以下是示例返回的完整结果集：
ManagerID EmployeeID Title Level
--------- ---------- --------------------------------------- ------
NULL 109 Chief Executive Officer 0
109 12 Vice President of Engineering 1
12 3 Engineering Manager 2
3 4 Senior Tool Designer 3
3 9 Design Engineer 3
3 11 Design Engineer 3
3 158 Research and Development Manager 3
3 263 Senior Tool Designer 3
3 267 Senior Design Engineer 3
3 270 Design Engineer 3
263 5 Tool Designer 4
263 265 Tool Designer 4
158 79 Research and Development Engineer 4
158 114 Research and Development Engineer 4
158 217 Research and Development Manager 4
(15 row(s) affected)请参阅
概念
其他资源
WITH common_table_expression (Transact-SQL)
EXCEPT 和 INTERSECT (Transact-SQL)
