一个BOM表，有child_id,parent_id
child_id parent_id
pm_888 pm_777
pm_777 pm_666
pm_777 pm_555
pm_666 pm_222
pm_555 pm_333
pm_333 pm_111
pm_222 pm_111现在取PM_888子节点的路径时怎么取成
pm_777,pm_666,pm_222,pm_111
pm_777,pm_555,pm_333,pm_111也就是一个零部件在BOM树中多次出现，任何解决?
谢谢！
child_id parent_id
pm_888 pm_777
pm_777 pm_666
pm_777 pm_555
pm_666 pm_222
pm_555 pm_333
pm_333 pm_111
pm_222 pm_111现在取PM_888子节点的路径时怎么取成
pm_777,pm_666,pm_222,pm_111
pm_777,pm_555,pm_333,pm_111也就是一个零部件在BOM树中多次出现，任何解决?
谢谢！
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (child_id VARCHAR(6),parent_id VARCHAR(6))
INSERT INTO @T
SELECT 'pm_888','pm_777' UNION ALL
SELECT 'pm_777','pm_666' UNION ALL
SELECT 'pm_777','pm_555' UNION ALL
SELECT 'pm_666','pm_222' UNION ALL
SELECT 'pm_555','pm_333' UNION ALL
SELECT 'pm_333','pm_111' UNION ALL
SELECT 'pm_222','pm_111'--SQL查询如下:;WITH Liang AS
(
SELECT
child_id,
parent_id,
path=CAST(parent_id AS VARCHAR(MAX)),
1 AS level
FROM @T
WHERE child_id='pm_888'
UNION ALL
SELECT
A.*,
B.path+'->'+A.parent_id,
B.level+1
FROM @T AS A
JOIN Liang AS B
ON A.child_id=B.parent_id
)
SELECT
path
FROM Liang
WHERE level=(
SELECT MAX(level)
FROM Liang
)/*
path
--------------------------------------------
pm_777->pm_555->pm_333->pm_111
pm_777->pm_666->pm_222->pm_111(2 行受影响)
*/
不过我用的是SQLSERVER2K，而且我希望的结果
是把BOM树上所有的节点路径都列出来
如：
PM_888 pm_777,pm_555,pm_333,pm_111
PM_888 pm_777,pm_666,pm_222,pm_111
pm_777 pm_555,pm_333,pm_111
pm_777 pm_666,pm_222,pm_111
...
再次感谢！
标题：查询指定节点及其所有子节点的函数
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-05-12
地点：广东深圳
*/create table tb(id varchar(3) , pid varchar(3) , name varchar(10))
insert into tb values('001' , null , '广东省')
insert into tb values('002' , '001' , '广州市')
insert into tb values('003' , '001' , '深圳市')
insert into tb values('004' , '002' , '天河区')
insert into tb values('005' , '003' , '罗湖区')
insert into tb values('006' , '003' , '福田区')
insert into tb values('007' , '003' , '宝安区')
insert into tb values('008' , '007' , '西乡镇')
insert into tb values('009' , '007' , '龙华镇')
insert into tb values('010' , '007' , '松岗镇')
go--查询指定节点及其所有子节点的函数
create function f_cid(@ID varchar(3)) returns @t_level table(id varchar(3) , level int)
as
begin
declare @level int
set @level = 1
insert into @t_level select @id , @level
while @@ROWCOUNT > 0
begin
set @level = @level + 1
insert into @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--调用函数查询001(广东省)及其所有子节点
select a.* from tb a , f_cid('001') b where a.id = b.id order by a.id
/*
id pid name
---- ---- ----------
001 NULL 广东省
002 001 广州市
003 001 深圳市
004 002 天河区
005 003 罗湖区
006 003 福田区
007 003 宝安区
008 007 西乡镇
009 007 龙华镇
010 007 松岗镇（所影响的行数为 10 行）
*/--调用函数查询002(广州市)及其所有子节点
select a.* from tb a , f_cid('002') b where a.id = b.id order by a.id
/*
id pid name
---- ---- ----------
002 001 广州市
004 002 天河区（所影响的行数为 2 行）
*/--调用函数查询003(深圳市)及其所有子节点
select a.* from tb a , f_cid('003') b where a.id = b.id order by a.id
/*
id pid name
---- ---- ----------
003 001 深圳市
005 003 罗湖区
006 003 福田区
007 003 宝安区
008 007 西乡镇
009 007 龙华镇
010 007 松岗镇（所影响的行数为 7 行）
*/drop table tb
drop function f_cid/*
标题：查询指定节点及其所有父节点的函数
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-05-12
地点：广东深圳
*/create table tb(id varchar(3) , pid varchar(3) , name varchar(10))
insert into tb values('001' , null , '广东省')
insert into tb values('002' , '001' , '广州市')
insert into tb values('003' , '001' , '深圳市')
insert into tb values('004' , '002' , '天河区')
insert into tb values('005' , '003' , '罗湖区')
insert into tb values('006' , '003' , '福田区')
insert into tb values('007' , '003' , '宝安区')
insert into tb values('008' , '007' , '西乡镇')
insert into tb values('009' , '007' , '龙华镇')
insert into tb values('010' , '007' , '松岗镇')
go--查询指定节点及其所有父节点的函数
create function f_pid(@id varchar(3)) returns @t_level table(id varchar(3))
as
begin
insert into @t_level select @id
select @id = pid from tb where id = @id and pid is not null
while @@ROWCOUNT > 0
begin
insert into @t_level select @id select @id = pid from tb where id = @id and pid is not null
end
return
end
go--调用函数查询002(广州市)及其所有父节点
select a.* from tb a , f_pid('002') b where a.id = b.id order by a.id
/*
id pid name
---- ---- ----------
001 NULL 广东省
002 001 广州市（所影响的行数为 2 行）
*/--调用函数查询003(深圳市)及其所有父节点
select a.* from tb a , f_pid('003') b where a.id = b.id order by a.id
/*
id pid name
---- ---- ----------
001 NULL 广东省
003 001 深圳市（所影响的行数为 2 行）
*/--调用函数查询008(西乡镇)及其所有父节点
select a.* from tb a , f_pid('008') b where a.id = b.id order by a.id
/*
id pid name
---- ---- ----------
001 NULL 广东省
003 001 深圳市
007 003 宝安区
008 007 西乡镇（所影响的行数为 4 行）
*/drop table tb
drop function f_pid
不过这样当一个零部件在BOM树中多次出现时会有问题啊
我想用SQL语句取出BOM树中所有部件的路径，谢谢！
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: [T]
IF OBJECT_ID('[T]') IS NOT NULL DROP TABLE [T]
CREATE TABLE [T] (child_id VARCHAR(6),parent_id VARCHAR(6))
INSERT INTO [T]
SELECT 'pm_888','pm_777' UNION ALL
SELECT 'pm_777','pm_666' UNION ALL
SELECT 'pm_777','pm_555' UNION ALL
SELECT 'pm_666','pm_222' UNION ALL
SELECT 'pm_555','pm_333' UNION ALL
SELECT 'pm_333','pm_111' UNION ALL
SELECT 'pm_222','pm_111'--SQL查询如下:GO
CREATE FUNCTION dbo.NodesPath(@Child_ID varchar(10))
RETURNS @t TABLE(child_id varchar(10),
parent_id varchar(10),
path varchar(8000),
level int)
AS
BEGIN
DECLARE @level int
SET @level=1
INSERT INTO @t
SELECT
child_id,
parent_id,
@Child_ID+'->'+parent_id,
@level
FROM T
WHERE child_id=@Child_ID
WHILE @@ROWCOUNT>0
BEGIN
SET @level=@level+1
INSERT INTO @t
SELECT
B.child_id,
B.parent_id,
A.path+'->'+B.parent_id,
@level
FROM @t AS A
JOIN T AS B
ON A.parent_id=B.child_id
AND A.level=@level-1
END
RETURN
END
GODECLARE @sql varchar(8000)
SET @sql=''SELECT @sql=@sql+' UNION ALL SELECT * FROM dbo.NodesPath('''+child_id+''') AS A'
+' WHERE NOT EXISTS(SELECT * FROM dbo.NodesPath('''+child_id+''')'
+' WHERE level>A.level)'
FROM (SELECT DISTINCT child_id FROM T) AS ASET @sql=STUFF(@sql,1,11,'')EXEC('
SELECT PATH
FROM ('+@sql+') AS A
ORDER BY level DESC
')GO
DROP FUNCTION dbo.NodesPath
DROP TABLE T/*
PATH
--------------------------------------------------------
pm_888->pm_777->pm_666->pm_222->pm_111
pm_888->pm_777->pm_555->pm_333->pm_111
pm_777->pm_666->pm_222->pm_111
pm_777->pm_555->pm_333->pm_111
pm_555->pm_333->pm_111
pm_666->pm_222->pm_111
pm_222->pm_111
pm_333->pm_111(8 行受影响)*/
因为我一个BOM树里可能有很多零部件，而且我最终需要的结果
是把零部件路径和零部件的其它属性合为一个结果集输出。
自己水平太差，汗下面的表是我用函数导出的临时结构表，我想操作这个表去路径
然后和零部件表JOIN输出
ID LEVEL PID
------------------------------------ ----------- ---
pm_9_241537 1
pm_9_241538 2 pm_9_241537
pm_9_241539 2 pm_9_241537
pm_9_215040 3 pm_9_241538
pm_9_215040 3 pm_9_241539
pm_9_215047 4 pm_9_215040
pm_9_215048 4 pm_9_215040
pm_9_215035 4 pm_9_215040
pm_9_215047 4 pm_9_215040
pm_9_215048 4 pm_9_215040
pm_9_215035 4 pm_9_215040
pm_9_215056 5 pm_9_215035
pm_9_215057 5 pm_9_215035
pm_9_215056 5 pm_9_215035
pm_9_215057 5 pm_9_215035
比如取pm_777的路径
想要的结果是
pm_777->pm_666->pm_222->pm_111
pm_777->pm_555->pm_333->pm_111
不过取出的结果有可能是
pm_666->pm_777->pm_222->pm_111
pm_666->pm_777->pm_333->pm_111
如果相同的零部件在BOM树处于相同的深度(比如都是处于第3级)
按你的方法是可行的,不过如果处在不同的深度(如一个处于第3级,一个处于第2级)
又会把不同路径的同种零部件路径取成相同的.郁闷啊ID Level pid
---------------------------------- ----------------------------
pm_215041 1
pm_215040 2 pm_215041
pm _208634 2 pm_215041
pm_215050 2 pm_215041
pm_215036 2 pm_215041
pm_215053 2 pm_215041
pm_215035 2 pm_215041
pm_215038 2 pm_215041
pm_215043 2 pm_215041
pm_215044 2 pm_215041
pm_215207 2 pm_215041
pm_215047 3 pm_215040
pm_215048 3 pm_215040
pm_215035 3 pm_215040
pm_215051 3 pm_215036
pm_215052 3 pm_215036
pm_215056 3 pm_215035
pm_215057 3 pm_215035
pm_215058 3 pm_215038
pm_215037 3 pm_215038
pm_215059 3 pm_215038
pm_215039 3 pm_215043
pm_215037 3 pm_215043
pm_214571 3 pm_215044
pm_214827 3 pm_215044
pm_215062 3 pm_215044
pm_215063 3 pm_215044
pm_214742 3 pm_215044
pm_214583 3 pm_215044
pm_215042 3 pm_215044
pm_215066 3 pm_215044
pm_214485 3 pm_215207
pm_214497 3 pm_215207
pm_208887 3 pm_215207
pm_214489 3 pm_215207
pm_214499 3 pm_215207
pm_214490 3 pm_215207
pm_214500 3 pm_215207
pm_214501 3 pm_215207
pm_214502 3 pm_215207
pm_214503 3 pm_215207
pm_208635 3 pm_215207
pm_208634 3 pm_215207
pm_214491 3 pm_215207
pm_208895 3 pm_215207
pm_214507 3 pm_215207
pm_208897 3 pm_215207
pm_208898 3 pm_215207
pm_214510 3 pm_215207
pm_214511 3 pm_215207
pm_214553 3 pm_215207
pm_215056 4 pm_215035
pm_215057 4 pm_215035
pm_214595 4 pm_214827
pm_214596 4 pm_214827