sqlserver 下用函数实现 oracle 的树查询( start with connect by ) 功能

/* SQLServer2005下自定义函数实现类似Oracle的树查询(start with) 功能 */
/* 已知信息 */
--实体树结点表的结构及初始数据如下:
CREATE TABLE T_NODE (
NODE_ID INT, --结点ID
NODE_NAME VARCHAR(255), --结点名称
P_NODE INT --父结点ID
)
INSERT INTOT_NODEVALUES (1,'根结点',0);
INSERT INTOT_NODEVALUES (2,'结点A',1);
INSERT INTOT_NODEVALUES (3,'结点B',1);
INSERT INTOT_NODEVALUES (4,'结点C',1);
INSERT INTOT_NODEVALUES (5,'结点A_1',2);
INSERT INTOT_NODEVALUES (6,'结点A_2',2);
INSERT INTOT_NODEVALUES (7,'结点A_3',2);
INSERT INTOT_NODEVALUES (8,'结点A_1_A',5);
INSERT INTOT_NODEVALUES (9,'结点A_1_B',5);
INSERT INTOT_NODEVALUES (10,'结点B_1',3);
INSERT INTOT_NODEVALUES (11,'结点B_2',3);
INSERT INTOT_NODEVALUES (12,'结点B_3',3);
/* ** ======================= ** */



/* 函数的实现 _方法1 * 普通的循环查询树 测试通过 ********
* 优点:快速遍历.
* 缺点:树的插入不是逐条插入的,而是按树的查询等级的顺序插入的
* ******* */
----创建函数---------
--DROP FUNCTION F_NODE_TREE
-- =============================================
-- Author: <liujianbin>
-- Create date: 2011-06-12
-- Description: 循环查询T_NODE树
-- =============================================
CREATE FUNCTION F_NODE_TREE(
@fId varchar(10)
)
RETURNS
@TMP_NODE_TREE TABLE(
ID INT, --结点ID
FID INT, --父结点ID
LEVEL INT --结点等级
)
AS
BEGIN
--定义变量
DECLARE @tmpLevel INT
--初始化
SET @tmpLevel=1
--插入第一个结点
INSERT @TMP_NODE_TREE SELECT T.NODE_ID, T.P_NODE, @tmpLevel FROM T_NODE T WHERE T.P_NODE=@fId
--插入数据不是空行,则循环插入下一级的结点数据
WHILE @@rowcount > 0
BEGIN
--级别加1
SET @tmpLevel=@tmpLevel+1
--根据上一级查询出来的父结点信息插入下一级数据
INSERT @TMP_NODE_TREE SELECT T.NODE_ID, T.P_NODE, @tmpLevel FROM T_NODE T ,@TMP_NODE_TREE b WHERE b.ID=T.P_NODE AND b.LEVEL=@tmpLevel-1
END
RETURN
END
-----------------------------------------
------调用示例 ---------------
SELECT T.* FROM F_NODE_TREE(0) T
/* ***** 方法1 结束 ***** */






/* 函数的实现 _方法2 ********** 逐条递归查询树 测试通过 ********
* 优点: 逐条递归查询插入,确保树的显示序列是按递归顺序排列(SID)
* 缺点: 性能上较 方法1 的慢 (查询大量数据的时候会比较明显),递归有32级限制.
* ******* */
----创建函数---------
--DROP FUNCTION F_NODE_TREE_REC
-- =============================================
-- Author: <liujianbin>
-- Create date: 2011-06-12
-- Description: 递归查询T_NODE树
-- =============================================
CREATE FUNCTION F_NODE_TREE_REC(
@fId VARCHAR(12) , --父结点ID
@level BIGINT , --当前级别,默认1
@sId BIGINT --当前插入序列,默认1
)
RETURNS
@TMP_TREE_TABLE TABLE(
ID BIGINT, --结点ID
FID BIGINT, --父结点ID
LEVEL BIGINT, --结点等级
SID BIGINT --插入序列
)
AS
BEGIN
-- 局部变量定义
DECLARE @tid BIGINT --临时ID
DECLARE @tfid BIGINT --临时父ID
DECLARE @sortId BIGINT --排列顺序ID
DECLARE @subId BIGINT --子结点ID
-- 本地游标定义
DECLARE @cur CURSOR
SET @cur = CURSOR LOCAL FOR SELECT NODE_ID,P_NODE from T_NODE where P_NODE=@fId
--数据初始化
SET @sortId = @sId
IF @sortId IS NULL OR @sortId < 1
SET @sortId = 1
if @level IS NULL OR @level < 0
SET @level = 0
SET @level = @level+1
SET @subId = NULL
-- 打开游标
OPEN @cur
FETCH NEXT FROM @cur INTO @tid,@tfid
WHILE @@rowcount > 0
BEGIN
INSERT INTO @TMP_TREE_TABLE VALUES(@tid,@tfid,@level, @sortId )
--插入顺序序列+1
SET @sortId = @sortId+1
--取当前结点的子结点,用来判断是否含有子结点
SELECT @subId = (SELECT TOP 1 T.NODE_ID FROM T_NODE T WHERE T.P_NODE=@tid)
--如果有子结点,则开始递归插入
IF @subId IS NOT NULL
BEGIN
-- 树形结构数据递归收集到建立的临时表中
INSERT @TMP_TREE_TABLE SELECT * from dbo.F_NODE_TREE_REC(@tid,@level,@sortId)
--设置下一个循环的序列为递归后的总合加当前值
SELECT @sortId =@sortId + (SELECT TOP 1 COUNT(*) FROM dbo.F_NODE_TREE_REC(@tid,@level,@sortId) )
END
--游标下移一位,进入下一个循环
FETCH NEXT FROM @cur INTO @tid,@tfid
END
CLOSE @cur
DEALLOCATE @cur
RETURN
END
----------------------------------------------
----调用示例----
SELECT T.* FROM F_NODE_TREE_REC(0,NULL,NULL) T ORDER BY T.SID
/* ***** 方法2 结束 **** */

评论

1 楼 jinsedeme 2012-02-01  

很好很强大