BOM死循环 超级难！！！！！ 欢迎大仙

Question

本帖最后由 moray126 于 2010-04-04 15:29:11 编辑

Answer 1

*
标题：查询各节点的父路径函数
作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开)  
时间：2008-05-12
地点：广东深圳
*//*
原始数据及要求结果如下：
--食品 
  --水果 
    --香蕉 
    --苹果    
  --蔬菜 
    --青菜
id          pid         name                 
----------- ----------- -------------------- 
1           0           食品
2           1           水果
3           1           蔬菜
4           2           香蕉
5           2           苹果
6           3           青菜要求得到各节点的父路径即如下结果：
id  pid name  路径                         
--- --- ----- ---------------
1   0   食品  食品
2   1   水果  食品,水果
3   1   蔬菜  食品,蔬菜
4   2   香蕉  食品,水果,香蕉
5   2   苹果  食品,水果,苹果
6   3   青菜  食品,蔬菜,青菜 
*/create table tb (id int , pid int , name nvarchar(20)) 
insert into tb values(1 , 0 , '食品')
insert into tb values(2 , 1 , '水果')
insert into tb values(3 , 1 , '蔬菜')
insert into tb values(4 , 2 , '香蕉')
insert into tb values(5 , 2 , '苹果')
insert into tb values(6 , 3 , '青菜')
go--查询各节点的父路径函数
create function f_pid(@id int) returns varchar(100)
as
begin
  declare @re_str as varchar(100)
  set @re_str = ''
  select @re_str = name from tb where id = @id
  while exists (select 1 from tb where id = @id and pid <> 0)
    begin
      select @id = b.id , @re_str = b.name + ',' + @re_str from tb a , tb b where a.id = @id and a.pid = b.id
    end
  return @re_str
end
goselect * , dbo.f_pid(id) 路径 from tb order by iddrop table tb
drop function f_pidSQL code
/*
标题：查询所有节点及其所有子节点的函数
作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开) 
时间：2009-04-12
地点：广东深圳
*/--生成测试数据 
create table tb(id varchar(10),pid varchar(10)) 
insert into tb select 'a', null 
insert into tb select 'b', 'a' 
insert into tb select 'c', 'a' 
insert into tb select 'd', 'b' 
insert into tb select 'e', 'b' 
insert into tb select 'f', 'c' 
insert into tb select 'g', 'c' 
go --创建用户定义函数 
create function f_getchild(@id varchar(10)) returns varchar(8000) 
as 
begin 
  declare @i int , @ret varchar(8000) 
  declare @t table(id varchar(10) , pid varchar(10) , level int) 
  set @i = 1 
  insert into @t select id , pid , @i from tb where id = @id 
  while @@rowcount <> 0 
  begin 
    set @i = @i + 1 
    insert into @t select a.id , a.pid , @i from tb a , @t b where a.pid = b.id and b.level = @i - 1
  end 
  select @ret = isnull(@ret , '') + id + ',' from @t 
  return left(@ret , len(@ret) - 1)
end 
go --执行查询 
select id , children = isnull(dbo.f_getchild(id) , '') from tb group by id
go --输出结果 
/* 
id         children     
---------- -------------
a          a,b,c,d,e,f,g
b          b,d,e
c          c,f,g
d          d
e          e
f          f
g          g（所影响的行数为 7 行）*/ --删除测试数据 
drop function f_getchild 
drop table tbSQL code
/*
标题：查询所有顶级节点及其子节点的例
地址：http://topic.csdn.net/u/20090323/21/63a91f51-c4df-464d-ba18-64343deb4e3a.html
作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开)
时间：2009-03-23
地点：广东深圳
*/[code=SQL]create table Area (id int identity,Name varchar(10) ,order_by int ,father_ID int )
insert into area values('广东省',2,0) 
insert into area values('四川省',2,0) 
insert into area values('湖北省',2,0) 
insert into area values('东莞市',1,1) 
insert into area values('广州市',1,1) 
insert into area values('天河区',0,5) 
insert into area values('绵阳市',1,2) 
insert into area values('武汉市',1,3) 
insert into area values('汉口区',0,8) 
insert into area values('随州市',1,3)
goselect * from areadrop table area/*
id          Name       order_by    father_ID   
----------- ---------- ----------- ----------- 
1           广东省        2           0
2           四川省        2           0
3           湖北省        2           0
4           东莞市        1           1
5           广州市        1           1
6           天河区        0           5
7           绵阳市        1           2
8           武汉市        1           3
9           汉口区        0           8
10          随州市        1           3（所影响的行数为 10 行）要求显示为：
name           
-------------- 
广东省
  东莞市
  广州市
    天河区
四川省
  绵阳市
湖北省
  武汉市
    汉口区
  随州市（所影响的行数为 10 行）
*/
SQL code
--创建原始表
create table Area (id int identity,Name varchar(10) ,order_by int ,father_ID int )
insert into area values('广东省',2,0) 
insert into area values('四川省',2,0) 
insert into area values('湖北省',2,0) 
insert into area values('东莞市',1,1) 
insert into area values('广州市',1,1) 
insert into area values('天河区',0,5) 
insert into area values('绵阳市',1,2) 
insert into area values('武汉市',1,3) 
insert into area values('汉口区',0,8) 
insert into area values('随州市',1,3)
--创建临时表
create table tmp (id int identity,Name varchar(10) ,order_by int ,father_ID int )
go--创建查询指定节点及其所有子节点的函数
create function f_cid(@ID int) returns @t_level table(id int , 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 area a , @t_Level b
    where a.father_ID = b.id and b.level = @level - 1
  end
  return
end
go--创建存储过程并将数据插入临时表
create proc my_proc 
as
begin
  declare @id as int
  set @id = 0
  while exists(select 1 from area where order_by = 2 and id > @id)
  begin
    set @id = (select min(id) from area where order_by = 2 and id > @id)
    insert into tmp(Name ,order_by ,father_ID) select a.name,a.order_by ,a.father_id from area a , f_cid(@id) b where a.id = b.id order by a.id 
  end
end
go
exec my_proc--从临时表提取数据并显示
select case when order_by = 2 then name
            when order_by = 1 then '  ' + name
            when order_by = 0 then '    ' + name
       end name
from tmp order by iddrop function f_cid
drop proc my_proc
drop table area , tmp/*
name           
-------------- 
广东省
  东莞市
  广州市
    天河区
四川省
  绵阳市
湖北省
  武汉市
    汉口区
  随州市（所影响的行数为 10 行）

Answer 2

--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (id int,name varchar(1),pid int)
insert into [tb]
select 1,'A',0 union all
select 2,'B',1 union all
select 3,'D',1 union all
select 4,'C',2 union all
select 5,'D',2 union all
select 6,'A',4 union all
select 7,'E',5 union all
select 8,'F',5
GOcreate proc sp_wsp
as
declare @tb table(id int,name varchar(10),pid int,fullpath varchar(100))
insert into @tb select *,name from tb
DECLARE @i int
DECLARE @j int
set @i=0
set @j=1
select @i=max(pid) from @tb
while @j<=@i
begin
update b set  fullpath=a.fullpath+'\'+b.name from @tb b inner join @tb a on b.pid=a.id where b.pid=@j 
set @j=@j+1
end
select id,name,fullpath from @tb where len(fullpath)-len(replace(fullpath,name,''))>1
goexec sp_wsp--结果：
id          name       fullpath
----------- ---------- -------
6           A          A\B\C\A

Answer 3

谢谢大仙。
不过路径不是单一的阿，比如说 E这个物品。 路径1 A-D-E，路径2 A-B-D-E。苦死俺了。

Answer 4

谢谢大仙。
不过还是有些不一样，就BOM来说，实际的数据结构不是大仙所提供的这个样子。
BOM表
物品id  部件id  需要数量  替代部件(等其他字段)
A       B      1
A       D      1
B       C      1
C       A      1
D       E      1
D       F      1
树形结构改成下面的样子可能会好些
            A 
          /   \ 
        B     |  
      /   \   /
    C       D 
  /       /   \ 
A       E       F感觉大仙提供的测试数据和实际不符合。
如果这样的话，还能解决吗？

Answer 5

BOM表结构。除了前两个字段，其他可以不用考虑。　　Part_no 母件代号 Char(24) 不能为空、重复，最大字符长度为24位
　　Part_no1 子件代号 Char(24) 不能为空，最大字符长度为24位
　　Yl_qty 用量 Numeric(8,4) 最长为8位，小数点4位，默认值为0
　　Bad_r 不良率 Numeric(7,4) 最长为7位、小数点4位，默认值为0
　　Stop 暂停 Char(1) 
　　Locator 工序号 Char(2) 不能为空、默认值为‘N’，最大字符长度为2位
　　No_pur1 暂停 Char(1) 默认值为‘N’
　　No_pur2 不发料 Char(1) 默认值为‘N’
　　Rem 位号 Varchar(250) 最长字符长度为250位
　　Gg_person 更改人 Chan(20) 最大字符长度为20位
　　Ecn_no ECN单号 Char(12) 最大字符长度12

Answer 6

I'm dead @ 苦思！

Answer 7

大家有没有遇到bom死循环的问题啊，请给个提示。

Answer 8

对，的确是异常。但是老板说了，要查！！！ 还说bom的编制是手工的，不能保证百分百正确，所以要写个程序来检查。

Answer 9

实际应用中物料清单很庞大，级数也很多。
----------------------------------------------------------
--用大乌龟的思路 try下  仅限测试数据...  慎用 
IF NOT OBJECT_ID('[tg]') IS NULL
    DROP TABLE [tg]
GO
CREATE TABLE [tg]([pid]  VARCHAR(10),[jid] VARCHAR(10))
go
INSERT [tg]
select 'A','B' union all
select 'A','D' union all
select 'B','C' union all
select 'C','A' union all
select 'D','E' union all
select 'D','F'
--创建用户定义函数 
alter function f_getcpc(@pid varchar(10),@jid varchar(10)) returns varchar(8000) 
as 
begin 
  declare @i int , @ret varchar(8000),@flag varchar(8000),@lmit int  
  declare @t table(pid varchar(10) , jid varchar(10) , level int) 
  set @i = 1 
  set @flag=0
  set @lmit=1000 
  insert into @t select pid , jid , @i from tg where pid = @pid and jid=@jid
  while @@rowcount <> 0 
  begin 
    select @ret = isnull(@ret , '') + jid + ',' from @T
      if(charindex(@pid,@ret)=0 and len(@ret)<@lmit)
begin
       set @i = @i + 1 
       insert into @t select a.pid , a.jid , @i from tg a , @t b where a.pid = b.jid and a.pid<>b.pid and b.level = @i - 1
        end
      else if(charindex(@pid,@ret)>0 or len(@ret)>@lmit)
begin
  set @flag=1
  break
end
  end 
if(@flag<>0)
begin
  set @ret=''
          select @ret = isnull(@ret , '') + jid + '->' from  @t
  set @flag=@pid+'->'+LEFT(@ret,CHARINDEX(@pid,@ret))
end
return @flag
end 
go --执行查询 
select *,dbo.f_getcpc(pid,jid) as deadlock from tg order by pid--结果
/*
pid    jid     deadlock
A B A->B->C->A
A D 0
B C B->C->A->B
C A C->A->B->D->C
D E 0
D F 0
*/

Answer 10

check error:
  if subitem in parentPath: return exists Error

Answer 11

谢谢这位大仙。不过计算的结果里面有个
C    A    C->A->B->D->C
实际上应该没有这个循环。我再三检查了我提供的数据，发现少了一条记录
完整的纪录如下：
物品id     部件id     需要数量     替代部件(等其他字段) 
A               B             1 
A               D             1 
B               C             1 
B               D             1 
C               A             1 
D               E             1 
D               F             1 另外还有个地方需要说明，数据库的纪录循序不一定是按照我提供的这个表，最终的bom是多个bom合并而成的，物品id的排序和bom级数也没有什么关系。

Answer 12

if object_id('[tb]') is not null drop table [tb]
create table [tb] (id int,name varchar(1),pid int)
insert into [tb]
select 1,'A',0 union all
select 2,'B',1 union all
select 3,'D',1 union all
select 4,'C',2 union all
select 5,'D',2 union all
select 6,'A',4 union all
select 7,'E',5 union all
select 8,'F',5
GO
;with cte
as
(
select   *,[path]=cast([name]+'->' as varchar(100)) ,[level] = 1 from tb where pid = 0
    union all
    select a.*,  cast(c.[path]+a.[name]+'->' as varchar(100)),[level]+1 from cte c ,tb a where a.pid = c.id
)
select 
* 
from cte
where len([path]) > 6 and right([path],3) = left([path],3)
/*
id          name pid         path           level
----------- ---- ----------- -------------- -----
6           A    4           A->B->C->A->     4(1 行受影响)
*/

Answer 13

还是纪录顺序的问题，记录出现的循序和物品所在BOM里的级数没有必然联系。
其实把bom表加上1到8这样的id，本身也是不容易的事情。

Answer 14

25楼正解,
判断有向闭合图的SQL经典方法,拼接Path字符串.

Answer 15

非也。
第一列的1-8这几个数是怎么回事？
一定要记住，物品的id和所在bom里的位置无关。
先把我提供的表的纪录打乱了，再来解。

Answer 16

------------------------------------------------------------------------
-- Author : happyflystone  
-- Date   : 2010-04-06 
-- Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) 
--       Apr 14 2006 01:12:25 
--       Copyright (c) 1988-2005 Microsoft Corporation
--       Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
--      
-------------------------------------------------------------------------- Test Data: ta
IF OBJECT_ID('[tb]') IS NOT NULL 
    DROP TABLE [tb]
Go
CREATE TABLE tb([cid] NVARCHAR(1),[pid] NVARCHAR(1))
Go
INSERT INTO tb
    SELECT 'A','B' UNION ALL
    SELECT 'A','D' UNION ALL
    SELECT 'B','C' UNION ALL
    SELECT 'B','D' UNION ALL
    SELECT 'C','A' UNION ALL
    SELECT 'D','E' UNION ALL
    SELECT 'D','F' 
GO
--Start
;with cte
as
(
    select   *,[path]=cast([cid]+'->' as varchar(100)) ,[level] = 1 
    from (select distinct cid,cast('' as nvarchar(1))  as pid from tb union  select distinct pid ,'' from tb) b 
    union all
    select a.*,cast(a.[cid]+'->'+c.[path] as varchar(100)),[level]+1 
    from cte c ,tb a 
    where a.pid = c.cid and charindex(a.[cid]+'->',c.[path])=0
)
select 
[path]+cid+'->'
from cte
where exists(select 2 from tb where cid+'->' = right([path],3) and pid+'->' = left([path],3))-- = left([path],3)
--Result:
/*
--------------
A->B->C->A->
C->A->B->C->
B->C->A->B->(3 行受影响)*/
--End 

Answer 17

tony哥，上次我没在QQ上，后来给你留言了，不好意思

Answer 18

不要等添加完后再去检查整个BOM，
而是在添加某一个节点时，就要检查是否包含了树上面的半成品。

Answer 19

应该在保存bom的时候，就判断会不会死循环。

Answer 20

找循环是应该用CTE，石头哥那个就很完美了，我再写个找重复的，用来放在触发器里检查重复
IF OBJECT_ID('[tb]') IS NOT NULL 
    DROP TABLE [tb]
IF OBJECT_ID('FUN_MU') IS NOT NULL DROP FUNCTION FUN_MU
Go
CREATE TABLE tb([cid] NVARCHAR(1),[pid] NVARCHAR(1))
Go
INSERT INTO tb
    SELECT 'A','B' UNION ALL
    SELECT 'A','D' UNION ALL
    SELECT 'B','C' UNION ALL
    SELECT 'B','D' UNION ALL
    SELECT 'C','A' UNION ALL
    SELECT 'D','E' UNION ALL
    SELECT 'D','F' 
GO
CREATE FUNCTION FUN_MU(@ID NVARCHAR(1))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @STR VARCHAR(8000)
DECLARE @T TABLE(TEMPID NVARCHAR(1))
INSERT INTO @T SELECT @ID
WHILE @@ROWCOUNT>0
BEGIN
IF EXISTS(SELECT 1 FROM TB WHERE CID IN (SELECT TEMPID FROM @T) AND PID IN (SELECT TEMPID FROM @T))
RETURN '有重复'
ELSE
INSERT INTO @T SELECT DISTINCT PID FROM TB WHERE CID IN (SELECT TEMPID FROM @T)
END
RETURN ''
END
GO
SELECT ID,DBO.FUN_MU(ID)
FROM (
SELECT CID 'ID' FROM TB
UNION
SELECT PID FROM TB
) T
/*
A 有重复
B 有重复
C 有重复
D 有重复
E
F
*/

Answer 21

学习学习……BOM真是难弄来着

Answer 22

 非常同意这个说法，楼主，不能仅仅从技术上来做这个BOM表，实际的情况是要考虑在流程中禁止这样的违法数据，本来就不应该产生A->B->C->A.
如果是多层次的BOM表，建议你分开来做。这样也简化查询表。否则将来数据一多，必然把BOM单查询死。

Answer 23

辛苦大家了。
现实中的BOM究竟是什么样子的，偶也不知道，姑且当作一个智力题来做吧。当然了做的好，对bom的处理应该有帮助。
目前来看，还没有满意的答案，希望大家多交流阿。

Answer 24

只是一个简单的猜测，
假使楼主是使用id和pid来确定关系的话，
并且是如楼主所说，出现两次a，
那么用select id,count(*) from table having count(*)>1
不就搜索出那个a了么？