(转)使用SQL2005 递归查询结合Row_Number()实现完全SQL端树排序

使用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:     < 将指定Int数据左填0到指定宽度 >
--   =============================================
CREATE FUNCTION dbo.Lpad 
( 
@i  int ,@len  int  
) 
RETURNS nvarchar(max) 
AS 
BEGIN 
RETURN cast (replicate( ' 0 ' , @len  -  len(@i) )  +  convert(nvarchar,@i)  as  nvarchar(max)) 
END 

 

--   =============================================
--  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端生成已排序的树的目的了，完全脱离了程序处理，这个方法看起来效率还不错呢~

我在此抛砖引玉了，各位如果有可以改进的地方，请贴出来~

 

原帖：http://www.cnblogs.com/kingimg/archive/2009/02/05/1384410.html