高分求助，sql查询问题

Question

有2张表，表a(f_item,s_item,amount)表b(item,dates).其中表a是产品结构的表，f_item代表父件产品，s_item代表子件产品，amount代表数量（该字段不需要用到）。表b是产品生产或采购周期表，item是产品，dates是天数。 a表里的f_item,s_item在b表中item字段里都有，即f_item = item 或s_item = item.
现在的问题是，给出一个产品，能够得到这么产品的交货期，即在sql里做一个函数或者存贮过程（例如叫f_getmaxdate），输入值为产品，返回值为交货期。
举个例子：电脑(1)由显示器（3），主机组成（1），主机由主板(2)和cpu(3)组成,括号里的数字代表生产或者采购的天数。则表a有4条记录：（电脑，显示器，''），（电脑,主机,''）,(主机，主板，''),(主机,cpu,'').表b有5条记录：(电脑，1)，（显示器，3），（主机，1）,（主板，2），（cpu,3）。分析过程是完成主机需要先采购主板和cpu，cpu时间最长，则取cpu得时间3天，生产主机要1天，则完成一个主机要 3+1=4天，而采购显示器需要3天，所以，第1级产品结构里取4天（完成主机的时间），则生产电脑需要4 + 1 = 5天。即f_getmaxdate(电脑) = 5天
这个例子只有2级，根节点电脑为0级，主机和显示器为第1级，以此类推。我的实际需求里面，会有很多级。
请教高手能写个代码给我，不胜感激。

Answer 1

举个例子：
表a结构：                                                                                表b结构：
f_item  s_item  amount                                                                item     dates
电脑      主机                                                                                     电脑        1
电脑      显示器                                                                                   主机        1
主机       主板                                                                                    显示器      3
主机       cpu                                                                          主板        2
                                                                                       cpu       3
做一个函数或者存储过程f_getmaxdate实现， f_getmaxdate（电脑） = 5这个例子只有2级，根节点电脑为0级，主机和显示器为第1级，以此类推。我的实际需求里面，会有很多级。 

Answer 2

需求太乱。最好贴数据出来，要不还得分析你的意思。现在时间就是金钱啊，在csdn里时间就是专家分啊

Answer 3

我晕,算lead time的.
这个要循环的,
你的思路和算法已经很清晰了,
只是写出来而已,
查找下级产品请参考BOM的例子.
老大的Blog上面有！

Answer 4

--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

Answer 5

 -- 然后配合这个查询就可以实现了,
declare @tb table([f_item] nvarchar(2),[s_item] nvarchar(3))
Insert @tb
select N'电脑',N'主机' union all
select N'电脑',N'显示器' union all
select N'主机',N'主板' union all
select N'主机',N'cpu'
--Select * from @tb
declare @tbB table([item] nvarchar(3),[dates] int)
Insert @tbB
select N'电脑',1 union all
select N'主机',1 union all
select N'显示器',3 union all
select N'主板',2 union all
select N'cpu',3
SELECT sum(dates) as d FROM @tbB B
LEFT JOIN @tb A ON A.[s_item] = B.[item]
WHERE ISNULL(A.f_item,b.item) ='电脑'
/*
d
-----------
5
*/

Answer 6

这个问题说简单点就是知道某个产品，根据bom结构表和产品交货期表，就能够得到这个产品的最大交货期。

Answer 7

都是强淫啊，原来SQL语句也可以写出这样的算法来
学习学习+

Answer 8

kingdomgps,我还以为你给我答案呢，不要乱留言，我在线等待呢。

Answer 9

弱弱地问一下,虽然那个5已经计算出来了,
但是发现有问题./*
电脑
  -- 主机
     -- 主板
     -- cpu
  -- 显示器
*/
所以5计算出来是有问题的....

Answer 10

苦行僧，你给我写个函数把，可以直接用的。我看csdn的关于bom的帖子，里面大部分都是遍历bom所有节点后，把所有的节点的数量（或者价格）都加在一起了，而我需要选择一条最大的值得路径，应该是max(路径1，路径2，。。)

Answer 11

谢谢了。在说简单点，表a(f_item,s_item)是结构表，代表父、子的关系，表b(item,dates)是节点对应的数值表。需要得到最大值，例如：
                                 2
                         3      2
                       1   2  3   1
                             1 2 3 10
结果就是：10+1+2+2=15

Answer 12

create table TA(f_item nvarchar(100),s_item nvarchar(100),amount int)
insert into TA select '电脑','主机',100
union select '电脑','显示器',20 union 
select '主机','主板',90 union 
 select '主板','cpu',78create table TB (item nvarchar(100),dates int)
insert into TB select '电脑',1 union select '主机',1 union  select '显示器',3create function  f_getmaxdate(@item nvarchar(100))
  returns  int
as
  begin
    declare @curItem nvarchar(100),@sum int,@sitems nvarchar(100)--
    select @sitems = isnull(@sitems,'')+s_item+',' from TA where f_item=@item
    
  
     select @sum = isnull(@sum,0)+dates from tb where item=@item--取得当前item的周期    while(@sitems <>'' and @sitems is not null)
     begin
        set  @curItem = substring(@sitems,1,charindex(',',@sitems)-1)--截取以‘，’号分隔的Item
        set @sitems = stuff(@sitems,1,charindex(',',@sitems),'')--截取掉当前的IT
         --把当前的Item对应的周期取出来
       select @sum=isnull(@sum,0)+dates from tb where item=@curItem
     end
     return @sum
  end
select dbo.f_getmaxdate('电脑')----------- 
5（所影响的行数为 1 行）

Answer 13

/*
         2 
      3  2 
1  2  3  1 
1  2  3 10 
*/
这个数据是什么意思?
我15楼说了,如果按照你最先的哪个例子,
那么那个5 ＝ 1＋1 ＋3是有问题的.
我觉得应该是,
/*
电脑(1)
  -- 主机(1)
     -- 主板(2)
     -- cpu(3)
  -- 显示器(3)
*/
那电脑算出来的应该是 10 ＝ 1＋1＋2＋3＋3.
先把算法所清楚。

Answer 14

/*
电脑(1)
  -- 主机(1)
     -- 主板(2)
     -- cpu(3)
  -- 显示器(3)
*/
这其实就是一棵树，我的需要就是得到树根到树叶的最大路径，或者说路径的最大值，明白吗？

Answer 15

补充一个问题，sql server 6.5 里面不能自定义函数吗？为什么我把zhiguo2008写的函数代码在查询分析器里面运行不可以呢？我用的就是sql server 6.5

Answer 16

看来，这个问题得改了，sql 6.5不支持函数，所以改成求一个存储过程了，其他的不变

Answer 17

我算出来是8算法是没有什么问题了，问题在于算术计算上怎么处理。
如果dates理解为个数，那么电脑有（表里数据）显示器，主板，CPU, 它自己的个数都不能加进去，比如电脑1
如果dates理解为本身的成本，那么就要把电脑 1算进去。 你在上面的描述，实在没看明白。set nocount on
create table tb([f_item] varchar(20),[s_item] varchar(20))
go
Insert tb
select '电脑','主机' union all
select '电脑','显示器' union all
select '主机','主板' union all
select '主机','cpu'
go
create table ta([item] varchar(20),[dates] int)
go
Insert ta
select '电脑',1 union all
select '主机',1 union all
select '显示器',3 union all
select '主板',2 union all
select 'cpu',3
go
--select * from tb
--select * from ta
--gocreate function getdates(@item varchar(10),@dates int)
returns int
as
begin
--declare @item varchar(10),@dates int
--select @item='电脑',@dates= 0
declare @f_item varchar(20),@o_item varchar(20)
declare @t table(id int identity(1,1),f_item varchar(20),s_item varchar(20))
set @o_item=@item

myloop:
begin
set @f_item=@item
select top 1 @dates=@dates + a.dates ,@item=b.s_item
from ta a
left join tb b
on b.f_item=a.item
where a.item=@f_item and not exists(select 1 from @t where f_item=a.item and s_item=b.s_item)

insert @t select @f_item,@item end if @item is not null
goto myloop else
begin select top 1 @item = b.f_item
from tb a
inner join @t b
on b.f_item=a.s_item
where exists(select 1 from tb x where f_item=a.s_item and not exists(select 1 from @t where f_item=x.f_item and s_item=x.s_item))
if @item is not null and @item !=@o_item
goto myloop end

return @datesend
goselect *,dbo.getdates(item,0) from ta
go
drop function dbo.getDates
gocreate proc sp_getdates(@item varchar(10),@dates int)
as
begin
--declare @item varchar(10),@dates int
--select @item='电脑',@dates= 0
declare @f_item varchar(20),@o_item varchar(20)
declare @t table(id int identity(1,1),f_item varchar(20),s_item varchar(20))
set @o_item=@item

myloop:
begin
set @f_item=@item
select top 1 @dates=@dates + a.dates ,@item=b.s_item
from ta a
left join tb b
on b.f_item=a.item
where a.item=@f_item and not exists(select 1 from @t where f_item=a.item and s_item=b.s_item)

insert @t select @f_item,@item end if @item is not null
goto myloop else
begin select top 1 @item = b.f_item
from tb a
inner join @t b
on b.f_item=a.s_item
where exists(select 1 from tb x where f_item=a.s_item and not exists(select 1 from @t where f_item=x.f_item and s_item=x.s_item))
if @item is not null and @item !=@o_item
goto myloop end

select @datesend
go
exec sp_getDates '电脑',0
go
drop proc sp_getdates
go
drop table ta,tb
go

Answer 18

     2 
      3  2 
1  2  3  1 
1  2  3 10 
我上面这是另外一个例子了，和电脑的没有关系，都是一个意思，但是看来，我的意思没有表达出来。
我那个问题说简单点就是个树的问题。随便一棵树，求叶子到根节点得最大距离（距离=叶子（对应的数值）+ 它的父节点（对应的数值） + 。+根节点（对应的数值））。表a是树的父子节点的对应关系。表b是每个节点对应的值，我的目的就是任意给一个根节点M（通过表a就可以得到一颗以M为根节点的树），从这棵以M为根节点的树的叶子到根节点，中间会经过一些节点，把节点对应的数值相加，就是距离。如果树有100个叶子，那么从每个叶子到根节点都有一个距离数，一共就会有100个距离，那么我要选出最大的一条了

Answer 19

还是苦行僧术语比较专业，我这个问题就是一句话：树的最大路径问题。
就是数据结构里面的树的最大路径问题，一模一样的。
表a是树的结构，表b是每个节点的值。任意给个根节点，根据表a得到树，然后根据表b计算这棵树的最大路径是多少。

Answer 20

在吧问题描述一遍，希望能说明白了：
表A(f_item,s_item)是父子节点的对应关系表,表B（item,dates）是每个节点对应的数值.需要一个存储过程，输入一个根节点号（item）,根据表A可以得到一棵树,我要得到叶子到根节点的最大距离,每个节点的数值在表B里可以得到。
dates值就是一个数字，可能是3，或者5，或者12。等等
结果应该是：max(每个叶子（的dates值）+ 它的的父节点的（的dates值）+ 它的的父节点的父节点的（的dates值) + 。 +根节点的（的dates值))求一存储过程，输入值是一个根节点，输出值就是我要找的最大值

Answer 21

补充一个问题，sql server 6.5 里面不能自定义函数吗？为什么我把zhiguo2008写的函数代码在查询分析器里面运行不可以呢？我用的就是sql server 6.5sql2000才开始支持自定义函数

Answer 22

create procedure p_getmaxdate
@item varchar(10),
@maxdates int output
as
set nocount on
--用一临时表保存结果,现假设每个层次f_item唯一,实际情况可能存在相同的组件同时位于不同的层级.
if object_id('tempdb..#tmp') is not null drop table #tmp
create table #tmp (f_item varchar(10),s_item varchar(10),dates int,state int)
 --state用于区分层级状态
insert into #tmp (f_item,s_item,dates,state) select '',item,0,0 from b where item=@item
while exists(select * from a,#tmp t where a.f_item=t.s_item and t.state=0)
begin
insert into #tmp (f_item,s_item,dates,state) 
select a.f_item,a.s_item,t.dates+isnull(b.dates,0),1
from #tmp t,a left join b on a.f_item=b.item where a.f_item=t.s_item and t.state=0
--将无子部件的记录state转为2
update t
set state=2
from #tmp t 
where state=1 and not exists(select * from a where f_item=t.s_item)
--删除上一层记录
delete #tmp where state=0
--将state=1的记录转为0
update #tmp set state=0 where state=1
endselect @maxdate=max(dates) from #tmp

Answer 23

忘记了删除临时表
create procedure p_getmaxdate
@item varchar(10),
@maxdates int output
as
set nocount on
--用一临时表保存结果,现假设每个层次f_item唯一,实际情况可能存在相同的组件同时位于不同的层级.
if object_id('tempdb..#tmp') is not null drop table #tmp
create table #tmp (f_item varchar(10),s_item varchar(10),dates int,state int)
 --state用于区分层级状态
insert into #tmp (f_item,s_item,dates,state) select '',item,0,0 from b where item=@item
while exists(select * from a,#tmp t where a.f_item=t.s_item and t.state=0)
begin
insert into #tmp (f_item,s_item,dates,state) 
select a.f_item,a.s_item,t.dates+isnull(b.dates,0),1
from #tmp t,a left join b on a.f_item=b.item where a.f_item=t.s_item and t.state=0
--将无子部件的记录state转为2
update t
set state=2
from #tmp t 
where state=1 and not exists(select * from a where f_item=t.s_item)
--删除上一层记录
delete #tmp where state=0
--将state=1的记录转为0
update #tmp set state=0 where state=1
endselect @maxdate=max(dates) from #tmp
drop table #tmp

Answer 24

运行结果：
Procedure 'p_getmaxdate' expects parameter '@maxdates', which was not supplied.

Answer 25

--这样调用,
试试小陈的那段代码可以不?
exec p_getmaxdate '01',null问一下LZ是2000还是2005,
2005的话可以使用CTE,这样循环方便些.
给你一个连接,求最短路径和最长路径算法的,基于2005的.
http://pratchev.blogspot.com/2008/02/shortest-path-for-friend-connections.htmlSQL与最短路径算法(2000)
http://datum.studyget.com/sh/200607/20060712_23110.shtml
P.S.
本来周末是看了一下的,
后来看其它帖子去了就忘了..
上面两个连接都是周末的时候找的...

Answer 26

用 exec p_getmaxdate '01',null
结果是：
命令已成功完成。
可是我看不到结果啊，结果应该是个数字才对，但是除了那句话，但是什么也没有。

Answer 27

原未作调试代码有点问题,现修改后再帖上
create table a (f_item varchar(10),s_item varchar(10),amount int null)
go 
insert into a (f_item,s_item)
select '电脑',      '主机'                                                                                  
union all select '电脑',      '显示器'                                                                      
union all select '主机',      '主板'                                                                        
union all select '主机',      'cpu'         
go
create table b (item varchar(10),dates int)
go
insert into b (item,dates)
select '电脑',        1 
union all select '主机',        1 
union all select '显示器',      3 
union all select '主板',        2 
union all select 'cpu',3
go
create procedure p_getmaxdate
        @item varchar(10),
        @maxdates int output
as
set nocount on
--用一临时表保存结果,现假设每个层次f_item唯一,实际情况可能存在相同的组件同时位于不同的层级.
if object_id('tempdb..#tmp') is not null drop table #tmp
create table #tmp (f_item varchar(10),s_item varchar(10),dates int,state int)
 --state用于区分层级状态
insert into #tmp (f_item,s_item,dates,state) select '',item,dates,0 from b where item=@item
while exists(select * from a,#tmp t where a.f_item=t.s_item and t.state=0)
begin
    insert into #tmp (f_item,s_item,dates,state) 
    select a.f_item,a.s_item,t.dates+isnull(b.dates,0),1
    from #tmp t,a left join b on a.s_item=b.item where a.f_item=t.s_item and t.state=0
    --将无子部件的记录state转为2
    update t
    set state=2
    from #tmp t 
    where state=1 and not exists(select * from a where f_item=t.s_item)
    --删除上一层记录
    delete #tmp where state=0
    --将state=1的记录转为0
    update #tmp set state=0 where state=1
endselect @maxdates=max(dates) from #tmp
drop table #tmp
go
declare @maxdates int
exec p_getmaxdate '电脑',@maxdates output
select @maxdates as 最大天数
最大天数        
----------- 
5

Answer 28

存储过程的输出参数要加上output
exec p_getmaxdate '电脑',@maxdates outputado command中,参数变量的Direction属性应设为2或3
adParamInputOutput 3 指示该参数既是输入参数，又是输出参数。 
adParamOutput 2 指示该参数是输出参数。 

Answer 29

谢谢小陈的帮助，你写的过程正是我想要的。
同时也要谢谢其他人：苦行僧，zhiguo2008，perfectaction，billlyh
结贴了，分数大家分了吧。
ps:
以上排名不分先后