有一个表
字段名 字段描述 数据类型 字段备注
TreeNo 分类树编号 VARCHAR(20) 不可为空=JY_TREE.TreeNo
NodeId 节点ID Smallint 在同一分类树里不可重复且为所有树节点的绝对顺序标志，第一个节点的ID是0。
NodeNo 节点编号 VARCHAR(20) 不可是空，且是同一分类树中另外一个不能重复的字段。
NodeVa 节点描述 VARCHAR(100) 不可是空
NodeSpe 节点性质 Smallint 目前均是“0”，暂未区分
NodeLvl 节点层级 Smallint 为节点在分类树中的层数，第一个节点的层数是0
ChildNum 子节点个数 Smallint 节点的直接下级节点个数。
ParentId 父节点ID Smallint 一定是排在前面的NodeId中的一个，第一个接点的ParentId是-1
Status 启用标志 Smallint 0：未启用 1：启用中 -1：已停用一部分数据
TreeNo NodeId NodeNo NodeVa NodeSpe NodeLvl ChildNum ParentId Status
D10 0 NO 数 0 0 7 -1 1
D10 1 K11 数的认识 0 1 6 0 1
D10 2 K12 数的种类 0 2 2 1 1
D10 3 K13 整数 0 3 3 2 1
D10 4 K14 分数 0 4 0 3 1
D10 5 K15 有理数 0 4 0 3 1
D10 6 K16 无理数 0 4 0 3 1
D10 7 K17 复理数 0 4 0 3 1我想获取某个结点的所有父结点，知道条件是TreeNo, NodeNO。
比如说知道“有理数”的TreeNo=D10,NodeNo=K16，那应该可以得到 无理数 - 整数 - 数的种类 - 数的认识 - 数 （正序倒序都行）
用SQL怎么实现呢
字段名 字段描述 数据类型 字段备注
TreeNo 分类树编号 VARCHAR(20) 不可为空=JY_TREE.TreeNo
NodeId 节点ID Smallint 在同一分类树里不可重复且为所有树节点的绝对顺序标志，第一个节点的ID是0。
NodeNo 节点编号 VARCHAR(20) 不可是空，且是同一分类树中另外一个不能重复的字段。
NodeVa 节点描述 VARCHAR(100) 不可是空
NodeSpe 节点性质 Smallint 目前均是“0”，暂未区分
NodeLvl 节点层级 Smallint 为节点在分类树中的层数，第一个节点的层数是0
ChildNum 子节点个数 Smallint 节点的直接下级节点个数。
ParentId 父节点ID Smallint 一定是排在前面的NodeId中的一个，第一个接点的ParentId是-1
Status 启用标志 Smallint 0：未启用 1：启用中 -1：已停用一部分数据
TreeNo NodeId NodeNo NodeVa NodeSpe NodeLvl ChildNum ParentId Status
D10 0 NO 数 0 0 7 -1 1
D10 1 K11 数的认识 0 1 6 0 1
D10 2 K12 数的种类 0 2 2 1 1
D10 3 K13 整数 0 3 3 2 1
D10 4 K14 分数 0 4 0 3 1
D10 5 K15 有理数 0 4 0 3 1
D10 6 K16 无理数 0 4 0 3 1
D10 7 K17 复理数 0 4 0 3 1我想获取某个结点的所有父结点，知道条件是TreeNo, NodeNO。
比如说知道“有理数”的TreeNo=D10,NodeNo=K16，那应该可以得到 无理数 - 整数 - 数的种类 - 数的认识 - 数 （正序倒序都行）
用SQL怎么实现呢
-- Author: liangCK 小梁 & angellan 兰儿
-- Date : 2009-05-27 13:45:33
-------------------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (TreeNo VARCHAR(3),NodeId INT,NodeNo VARCHAR(3),NodeVa VARCHAR(8),NodeSpe INT,NodeLvl INT,ChildNum INT,ParentId INT,Status INT)
INSERT INTO @T
SELECT 'D10',0,'NO','数',0,0,7,-1,1 UNION ALL
SELECT 'D10',1,'K11','数的认识',0,1,6,0,1 UNION ALL
SELECT 'D10',2,'K12','数的种类',0,2,2,1,1 UNION ALL
SELECT 'D10',3,'K13','整数',0,3,3,2,1 UNION ALL
SELECT 'D10',4,'K14','分数',0,4,0,3,1 UNION ALL
SELECT 'D10',5,'K15','有理数',0,4,0,3,1 UNION ALL
SELECT 'D10',6,'K16','无理数',0,4,0,3,1 UNION ALL
SELECT 'D10',7,'K17','复理数',0,4,0,3,1--SQL查询如下:DECLARE @TreeNo VARCHAR(3),@NodeNo VARCHAR(3);
SET @TreeNo = 'D10'
SET @NodeNo = 'K16';WITH LiangAndLan AS --小梁爱兰儿
(
SELECT
TreeNo,
NodeId,
NodeVa,
ParentId
FROM @T
WHERE TreeNo=@TreeNo AND NodeNo=@NodeNo
UNION ALL
SELECT
B.TreeNo,
B.NodeId,
B.NodeVa,
B.ParentId
FROM LiangAndLan AS A
JOIN @T AS B
ON A.ParentId=B.NodeId AND A.TreeNo=B.TreeNo
)
SELECT * FROM LiangAndLan/*
TreeNo NodeId NodeVa ParentId
------ ----------- -------- -----------
D10 6 无理数 3
D10 3 整数 2
D10 2 数的种类 1
D10 1 数的认识 0
D10 0 数 -1(5 row(s) affected)*/
--就用树型表,可到N级.参考如下:create table tb(id int, name varchar(10), pid int, px int)
insert into tb values(0 , '栏目分类', 0 , 1)
insert into tb values(1 , '动物' , 0 , 1)
insert into tb values(2 , '视频' , 0 , 2)
insert into tb values(3 , '老虎' , 1 , 1)
insert into tb values(4 , '狮子' , 1 , 2)
insert into tb values(5 , '搞笑' , 2 , 1)
go--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID int) RETURNS @t_Level TABLE(ID int,Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level
FROM tb a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END
RETURN
END
GO --调用函数查询id = 1及其所有子节点
SELECT a.* FROM tb a,f_Cid(1) b WHERE a.ID=b.ID
/*
id name pid px
----------- ---------- ----------- -----------
1 动物 0 1
3 老虎 1 1
4 狮子 1 2
（所影响的行数为 3 行）
*/drop table tb
drop function dbo.f_cid
----------------------------------BOM算法
--产品配件清单查询示例(邹建)
CREATE TABLE Item(ID int,Name varchar(10),Wast decimal(2,2))
INSERT Item SELECT 1,N'A产品',0.01
UNION ALL SELECT 2,N'B产品',0.02
UNION ALL SELECT 3,N'C产品',0.10
UNION ALL SELECT 4,N'D配件',0.15
UNION ALL SELECT 5,N'E物料',0.03
UNION ALL SELECT 6,N'F物料',0.01
UNION ALL SELECT 7,N'G配件',0.02CREATE TABLE Bom(ItemID int,ChildId int)
INSERT Bom SELECT 1,4
UNION ALL SELECT 1,7 --A产品由D配件和G配件组成
UNION ALL SELECT 2,1
UNION ALL SELECT 2,6
UNION ALL SELECT 2,7 --B产品由F物料及G配件组成
UNION ALL SELECT 4,5
UNION ALL SELECT 4,6 --D配件由F物料组成
UNION ALL SELECT 3,2
UNION ALL SELECT 3,1 --C产品由A产品和B产品组成
GOCREATE FUNCTION f_Bom(
@ItemIDs varchar(1000), --要查询物料清单及生产量的产品编号列表(逗号分隔)
@Num int --要生产的数量
)RETURNS @t TABLE(ItemID int,ChildId int,Nums int,Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t SELECT a.ItemID,a.ChildId,ROUND(@Num/(1-b.Wast),0),@Level
FROM Bom a,Item b
WHERE a.ChildId=b.ID
AND CHARINDEX(','+RTRIM(a.ItemID)+',',','+@ItemIDs+',')>0
WHILE @@ROWCOUNT>0 and @Level<140
BEGIN
SET @Level=@Level+1
INSERT @t SELECT a.ItemID,b.ChildId,ROUND(a.Nums/(1-c.Wast),0),@Level
FROM @t a,Bom b,Item c
WHERE a.ChildId=b.ItemID
AND b.ChildId=c.ID
AND a.Level=@Level-1
END
RETURN
END
GO--调用函数展开产品1、2、3的结构及计算生产10个产品时,各需要多少个配件
SELECT a.ItemID,ItemName=b.Name,
a.ChildId,ChildName=c.Name,
a.Nums,a.Level
FROM f_Bom('1,2,3',10) a,Item b,Item c
WHERE a.ItemID=b.ID
AND a.ChildId=c.ID
ORDER BY a.ItemID,a.Level,a.ChildId/*
ItemID ItemName ChildId ChildName Nums Level
----------- ---------- ----------- ---------- ----------- -----------
1 A产品 4 D配件 12 1
1 A产品 7 G配件 10 1
1 A产品 5 E物料 12 2
1 A产品 6 F物料 12 2
2 B产品 1 A产品 10 1
2 B产品 6 F物料 10 1
2 B产品 7 G配件 10 1
2 B产品 4 D配件 12 2
2 B产品 7 G配件 10 2
2 B产品 5 E物料 12 3
2 B产品 6 F物料 12 3
3 C产品 1 A产品 10 1
3 C产品 2 B产品 10 1
3 C产品 1 A产品 10 2
3 C产品 4 D配件 12 2
3 C产品 6 F物料 10 2
3 C产品 7 G配件 10 2
3 C产品 7 G配件 10 2
3 C产品 4 D配件 12 3
3 C产品 5 E物料 12 3
3 C产品 6 F物料 12 3
3 C产品 7 G配件 10 3
3 C产品 5 E物料 12 4
3 C产品 6 F物料 12 4(24 row(s) affected)
*/
drop table item
drop table bom
drop function f_Bom
-- Author: liangCK 小梁 & angellan 兰儿
-- Date : 2009-05-27 13:51:19
-------------------------------------------
--> 生成测试数据: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
CREATE TABLE [tb] (TreeNo VARCHAR(3),NodeId INT,NodeNo VARCHAR(3),NodeVa VARCHAR(8),NodeSpe INT,NodeLvl INT,ChildNum INT,ParentId INT,Status INT)
INSERT INTO [tb]
SELECT 'D10',0,'NO','数',0,0,7,-1,1 UNION ALL
SELECT 'D10',1,'K11','数的认识',0,1,6,0,1 UNION ALL
SELECT 'D10',2,'K12','数的种类',0,2,2,1,1 UNION ALL
SELECT 'D10',3,'K13','整数',0,3,3,2,1 UNION ALL
SELECT 'D10',4,'K14','分数',0,4,0,3,1 UNION ALL
SELECT 'D10',5,'K15','有理数',0,4,0,3,1 UNION ALL
SELECT 'D10',6,'K16','无理数',0,4,0,3,1 UNION ALL
SELECT 'D10',7,'K17','复理数',0,4,0,3,1--SQL查询如下:
GO-- 小梁 爱 兰儿CREATE FUNCTION dbo.f_tb_Tree(@TreeNo VARCHAR(3),@NodeNo VARCHAR(3))
RETURNS @t TABLE(TreeNo VARCHAR(3),NodeId INT,NodeNo VARCHAR(3),
NodeVa VARCHAR(20),ParentId INT,level INT)
AS
BEGIN
DECLARE @level INT;
SET @level = 1; INSERT @t SELECT TreeNo,NodeId,NodeNo,NodeVa,ParentId ,@level
FROM tb WHERE TreeNo=@TreeNo AND NodeNo=@NodeNo; WHILE @@ROWCOUNT > 0
BEGIN
SET @level =@level +1;
INSERT @t
SELECT B.TreeNo,B.NodeId,B.NodeNo,B.NodeVa,B.ParentId,@level
FROM @t AS A
JOIN tb AS B
ON A.ParentId=B.NodeId AND A.TreeNo=B.TreeNo
AND A.level = @level-1;
END RETURN;
END
GOSELECT * FROM dbo.f_tb_Tree('D10','K16')GODROP TABLE tb;
DROP FUNCTION dbo.f_tb_Tree