在实际应用中,我们经常需要用到树型结构功能,数据库结构一般如下
即用一个ParentID来标识该节点从属关系。为了最终生成一棵树,一般做法是把记录选出来,然后在程序里递归重新排好序后再呈现出来,但是如果有大量数据,就带来了性能开销问题。那么能不能直接在数据库利用SQL语句排好树后再输出呢?
SQL2005 有个递归查询功能也就是WITH .. AS 语句。对上面这样的表格使用递归查询,可以查询得到某树支下(包括根)的所有节点记录。类似语句如下:
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
将得到ClassID为1的根节点下的所有记录:
但是这个记录集显然没有经过树排序,这时还需要程序里进一步处理才能输出到客户端。在这里我介绍一种WITH 结合 Row_Number() 实现SQL端排序的方法。
先来看看最终的代码:
![](https://i-blog.csdnimg.cn/blog_migrate/34031c708bfe702fe82d01ff5c6593aa.gif)
-- =============================================
-- 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
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
执行以上存储过程,最后就输出结果:
这棵树已经从上到下按树结构排好序了!程序里只要原样输出即可!
Lpad函数将指定Int型数据左填0,按指定位数输出。关于为什么要用nvarchar(max)的问题,因为其它固定长度时,在递归查询里的rc.treepath + dbo.Lpad(..)时会改变长度,导致查询错误,使用max长度就避免了这个问题。当然,你也可以用固定长度,相加后再convert回来。
好了,这样子我们就实现了完全SQL端生成已排序的树的目的了,完全脱离了程序处理,这个方法看起来效率还不错呢~
我在此抛砖引玉了,各位如果有可以改进的地方,请贴出来~
原帖:http://www.cnblogs.com/kingimg/archive/2009/02/05/1384410.html