SQL Server 2000中处理树形结构的三个用户自定义函数

CREATE        FUNCTION dbo.udf_GetLeaf_1
(
@bprod AS NVARCHAR(15)
)
RETURNS @temp_mbm TABLE (bprod NVARCHAR(15))
AS
BEGIN
DECLARE @temp_mbm_1 TABLE (LEVEL INT,bprod NVARCHAR(15),childrenCount INT)
DECLARE @level INT
DECLARE @row INT
SET @level=0

--插入第一笔数据
INSERT INTO @temp_mbm_1(LEVEL,bprod,childrenCount)
SELECT @level,@bprod,COUNT(*)
FROM mbm
WHERE bprod=@bprod

SET @row=@@rowcount
WHILE @row>0
BEGIN
 SET @level=@level+1
 
 --取得当前层次的所有有孩子的料号的子料号
 --以及它们的和每个子料号的子料数
 INSERT INTO @temp_mbm_1(LEVEL,bprod,childrenCount)
 SELECT @level,bchld,(SELECT COUNT(*) FROM mbm x WHERE x.bprod=y.bchld)
 FROM mbm y
 WHERE bprod IN (SELECT bprod FROM @temp_mbm_1 WHERE childrenCount>0)
 
 SET @row=@@rowcount

 --删除已经取了孩子的非叶子料号
 DELETE FROM @temp_mbm_1
 WHERE level=@level-1
 AND childrenCount>0
END
INSERT INTO @temp_mbm SELECT bprod FROM @temp_mbm_1
RETURN
END

---

CREATE        FUNCTION dbo.udf_GetRoot_1
(
@bchld AS NVARCHAR(15)
)
RETURNS @temp_mbm TABLE (bprod NVARCHAR(15))
AS
BEGIN
DECLARE @temp_mbm_1 TABLE (LEVEL INT,bprod NVARCHAR(15),fatherCount INT)
DECLARE @level INT
DECLARE @row INT
SET @level=0

--插入第一笔数据
INSERT INTO @temp_mbm_1(LEVEL,bprod,fatherCount)
SELECT @level,@bchld,COUNT(*)
FROM mbm
WHERE bchld=@bchld

SET @row=@@rowcount
WHILE @row>0
BEGIN
 SET @level=@level+1

 --取得当前层次的有父亲的所有节点的父节点
 --以及它们的父节点的父节点数
 INSERT INTO @temp_mbm_1(LEVEL,bprod,fatherCount)
 SELECT @level,bprod,(SELECT COUNT(*) FROM mbm x WHERE x.bchld=y.bprod)
 FROM mbm y
 WHERE bchld IN (SELECT bprod FROM @temp_mbm_1 WHERE fatherCount>0)
 
 SET @row=@@rowcount

 --删除已经取过了父节点的非根节点
 DELETE FROM @temp_mbm_1
 WHERE level=@level-1
 AND fatherCount>0
END
INSERT INTO @temp_mbm SELECT bprod FROM @temp_mbm_1
RETURN
END

---

CREATE          FUNCTION dbo.udf_GetSubTreeDepth_1
(
@bprod AS NVARCHAR(15)
)
RETURNS @temp_mbm TABLE (LEVEL INT,ItemCount INT,LeafCount INT,BranchCount INT)
AS
BEGIN
DECLARE @temp_mbm_1 TABLE (LEVEL INT,bprod NVARCHAR(15),childrenCount INT)
DECLARE @level INT
DECLARE @row INT
SET @level=0

--插入第一笔数据
INSERT INTO @temp_mbm_1(LEVEL,bprod,childrenCount)
SELECT @level,@bprod,COUNT(*)
FROM mbm
WHERE bprod=@bprod

SET @row=@@rowcount
WHILE @row>0
BEGIN
 --取得层次的节点总数,其中的叶节点总数,非叶节点总数
 INSERT INTO @temp_mbm(LEVEL,ItemCount,LeafCount,BranchCount)
 SELECT LEVEL,COUNT(*),
 SUM(CASE WHEN childrenCount=0 THEN 1 ELSE 0 END) AS LeafCount,
 SUM(CASE WHEN childrenCount>0 THEN 1 ELSE 0 END) AS BranchCount
 FROM @temp_mbm_1
 WHERE level=@level
 GROUP BY LEVEL

 SET @level=@level+1
 
 --取得当前层次的所有有子节点的节点的所有子节点
 --以及这些子节点的子节点数
 INSERT INTO @temp_mbm_1(LEVEL,bprod,childrenCount)
 SELECT @level,bchld,(SELECT COUNT(*) FROM mbm x WHERE x.bprod=y.bchld)
 FROM mbm y
 WHERE bprod IN (SELECT bprod FROM @temp_mbm_1 WHERE childrenCount>0)
 
 SET @row=@@rowcount

 --删除已经取过子节点的节点
 DELETE FROM @temp_mbm_1
 WHERE level=@level-1
 AND childrenCount>0
END
RETURN
END