急，请教高手，详见内！

[flyingbird_zz]

有数据如下：

代码1 代码2 代码1/代码2
A B 2
B C 3
B D 2
C E 6
……

请问如何很快的找到A与子项E的关系？也就是A/E＝？

[flyingbird_zz]

对不起，各位大侠，我刚刚大概没有表达清楚，我并不仅仅是要知道单纯A、E的关系，而是要知道所有第一父项物料与其下所有子项物料的对应换算关系。

假设数据如下：
……
代码1 代码2 代码1/代码2
A1 B 2
B C 3
B D 2
C E 6

A2 B 2
B D 3
C E 6
……

我想得到如下结果：
父项 子项 父项/子项
……
A1 B ?
A1 C ?
A1 D ?
A1 E ?

A2 B ?
A2 C ?
A2 D ?
A2 E ?
……

上面的?代表计算出来的结果。

请问大家该如何写？

[子陌红尘]

create table BOM(CODE1 varchar(4),CODE2 varchar(4),[CODE1/CODE2] int)
insert into BOM select 'A','B',2
insert into BOM select 'B','C',3
insert into BOM select 'B','D',2
insert into BOM select 'C','E',6
go


create function F_GetQuotient(@Code1 varchar(4),@Code2 varchar(4))
returns int
as
begin
declare @Quotient int
set @Quotient=1

while (@Code1!=@Code2)
begin
select
@Quotient=@Quotient*[CODE1/CODE2],
@Code2=CODE1
from BOM where CODE2=@Code2

if @@rowcount=0
return null --非父子关系，返回空值
end

return @Quotient
end
go



select
c.*
from
(select
a.CODE1,
b.CODE2,
dbo.F_GetQuotient(a.CODE1,b.CODE2) as relation
from
BOM a,BOM b
where
not exists(select 1 from BOM where CODE2=a.CODE1)) c
where
c.relation is not null

/*
CODE1 CODE2 relation
----- ----- -----------
A B 2
A C 6
A D 4
A E 36
*/


drop function F_GetQuotient
drop table BOM

[flyingbird_zz]

libin_ftsafe(子陌红尘)，结果不对啊 ：（

好像还少了一个循环

[李雅勇的技术专栏]

子陌红尘又出马了，我在这儿看着吧... ...

[新鲜鱼排]

-----------------------------------------------------------------
--2005.12.23,10:15
------------------------------------------------------------------
--http://community.csdn.net/Expert/topic/4470/4470743.xml?temp=.2490503
--The example of querying tree datas
--Author: zjcxc

if exists (select * from dbo.sysobjects where id = object_id(N'[tb]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tb]
GO

--test data
create table [tb]([id] int identity(1,1),[pid] int,name varchar(20))
insert [tb] select 0,'China'
union all select 0,'American'
union all select 0,'Canada'
union all select 1,'BeiJing'
union all select 1,'ShangHai'
union all select 1,'JiangSu'
union all select 6,'SuZhou'
union all select 7,'ChangShu'
union all select 6,'NanJing'
union all select 6,'WuXi'
union all select 2,'New York'
union all select 2,'San Francisco'
go


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_cid]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_cid]
GO

/*--Tree data

Querying all of the children of the very id.

--zjcxc 2003-12()--*/

/*--test data

--
select a.*,level=b.[level] from [tb] a,f_cid(2)b where a.[id]=b.[id]
--*/
create function f_cid(
@id int
)returns @re table([id] int,[level] int)
as
begin
declare @l int
set @l=0
insert @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.[id],@l
from [tb] a,@re b
where a.[pid]=b.[id] and b.[level]=@l-1
end
/*--If you want to show the leaves which have no leaves, you can add the actions of deleting
delete a from @re a
where exists(
select 1 from [tb] where [pid]=a.[id])
--*/
return
end
go

select a.*,level=b.[level] from [tb] a,f_cid(2)b where a.[id]=b.[id]
go



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_pid]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_pid]
GO

/*--tree data

Querying all of the parents of the very id.

--zjcxc 2003-12()--*/

/*--

select a.* from [tb] a,f_pid(7)b where a.[id]=b.[id]
--*/
create function f_pid(
@id int
)returns @re table([id] int,[level] int)
as
begin
declare @l int
set @l=0
insert @re select [pid],@l from [tb] where [id]=@id and [pid]<>0
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.[pid],@l
from [tb] a,@re b
where a.[id]=b.[id] and b.[level]=@l-1 and a.[pid]<>0
end
return
end
go

select a.* from [tb] a,f_pid(7)b where a.[id]=b.[id]
go



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_id]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_id]
GO

/*--

the parameter of level and index

--zjcxc 2003-12()--*/

/*--

--use function to show different levels
select replicate('-',b.[level]*4)+a.name
from [tb] a,f_id()b
where a.[id]=b.[id]
order by b.sid
--*/
create function f_id()
returns @re table([id] int,[level] int,sid varchar(8000))
as
begin
declare @l int
set @l=0
insert @re select [id],@l,right(10000+[id],4)
from [tb] where [pid]=0
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.[id],@l,b.sid+right(10000+a.[id],4)
from [tb] a,@re b
where a.[pid]=b.[id] and b.[level]=@l-1
end
return
end
go

select replicate('-',b.[level]*4)+a.name
from [tb] a,f_id()b
where a.[id]=b.[id]
order by b.sid
go


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_copyid]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_copyid]
GO

/*--

Copy all of children of a point to another point,
and the aimed point couldn't has children.

--zjcxc 2005-01()--*/

/*--

--use ps to look forward to copying
exec p_copyid 2,10
select * from [tb]
--*/
create proc p_copyid
@s_id int,--the source point which you want to copy
@d_id int--the aimed point
as
if not exists(select * from [tb] where [id]=@d_id)
begin
raiserror(N'The aimed point "%d" does not exist!',1,16,@d_id)
return
end

declare @s nvarchar(4000)
select @s=N'create table #t([nid] int identity('
+rtrim(max([id])+1)--the copyed point is equal to the value of the largest point in the table plus 1.
+',1),[id] int,[pid] int,level int,[name] nvarchar(50))
declare @l int
set @l=0
insert #t([id],[pid],level,[name]) select [id],@d_id,@l,[name]
from [tb] where [pid]=@s_id
while @@rowcount>0
begin
set @l=@l+1
insert #t([id],[pid],level,[name])
select a.[id],b.nid,@l,a.[name]
from [tb] a,#t b
where a.[pid]=b.[id]
and b.level=@l-1
end
set identity_insert [tb] on
insert [tb]([id],[pid],[name])
select nid,[pid],[name] from #t
set identity_insert [tb] off'
from [tb]
exec sp_executesql @s
,N'@s_id int,@d_id int'
,@s_id,@d_id
go

--delete test
drop table [tb]
drop function f_cid
drop function f_pid
drop function f_id
drop proc p_copyid
go


--Level

--
create table [tb]([id] int identity primary key,[pid] int,[num] int)
create index idx_pid on [tb]([pid])
insert [tb] select 0,200
union all select 1,100
union all select 2,100
union all select 1,200
union all select 4,300
union all select 5,100
go

--ps used to count
create proc p_calc
as
set nocount on
declare @l int
set @l=1

select [id],[pid],[sumnum]=[num]
,level=case
when exists(select * from [tb] where [pid]=a.[id])
then @l-1 else @l end
into [#] from [tb] a
if @@rowcount>0
create index IDX_#_id_pid on [#]([id],[pid])
else
set @l=999

while @@rowcount>0 or @l=1
begin
set @l=@l+1
update a set level=@l,[sumnum]=isnull(a.[sumnum],0)+isnull(b.[sumnum],0)
from [#] a,(
select aa.pid,[sumnum]=sum(aa.[sumnum])
from [#] aa,(
select distinct [pid] from [#]
where level=@l-1
)bb where aa.[pid]=bb.[pid]
AND NOT EXISTS(
SELECT * FROM [#] WHERE [PID]=aa.[PID] AND [Level]=0)
GROUP BY aa.[PID]
having sum(case when aa.level=0 then 1 else 0 end)=0
)b where a.[id]=b.[pid]
end
select a.*,b.[sumnum]
from [tb] a,[#]b
where a.[id]=b.[id]
go

--use ps to count
exec p_calc
go

--delete test
drop table [tb]
drop proc p_calc

/*--test result

dm name pid [num] yj
----------- ---------- ----------- ----------- -----------
1 1 1 100 500
2 2 1 200 400
3 3 2 100 200
4 4 3 100 100

（the count of affected rows is 4）
--*/

[jwt1982]

??????

select 代码1/代码2 from tablename where 代码1=A and 代码2=E
你是想要这个?

[flyingbird_zz]

我要一个通用的sql语句，这只是一点点数据，真正表里面数据量非常的大，而且递归层次很多

[flyingbird_zz]

其实我是想知道在一个物料清单里面，如何很快的能够得出父项物料与其下某层子项物料的计算关系，我想要一个建单的递归算法。

[子陌红尘]

declare @t table(代码1 varchar(4),代码2 varchar(4),[代码1/代码2] int)
insert into @t select 'A','B',2
insert into @t select 'B','C',3
insert into @t select 'B','D',2
insert into @t select 'C','E',6

declare @Code1 varchar(4),@Code2 varchar(4),@Quotient int
set @Code1='A'
set @Code2='E'
set @Quotient=1

while @@rowcount<>0 and (@Code1!=@Code2)
begin
select
@Quotient=@Quotient*[代码1/代码2],
@Code2=代码1
from @t where 代码2=@Code2
end

select @Quotient as [A/E]

/*
A/E
----
36
*/

[-狙击手-]

create table testt(代码1 char(1),代码2 char(1),val int)
insert testt select 'A','B',2
insert testt select 'B','C',3
insert testt select 'B','D',2
insert testt select 'C','E',6

go
--创建函数
--函数返回一个表，根据实际情况的不同一层一层的插入，可以充分利用生成的表
create function f_go(@col varchar(10))
returns @t table(col varchar(30),st varchar(20),ed varchar(20),val int,level int)
as
begin
declare @i int
set @i=1
insert @t select 代码1+'-'+代码2,*,@i from testt where 代码1=@col
while exists (select * from testt a,@t b where
b.ed=a.代码1 and b.level=@i and b.ed<>@col )
begin
set @i=@i+1
insert @t
select b.col+'-'+a.代码2,a.代码1,a.代码2,b.val*a.val,@i from testt a,@t b
where b.level=@i-1 and b.ed=a.代码1 and b.ed<>@col
end
return
end
go

select 'A' as 代码1 ,'E' as 代码2,
a.val
from (select * from dbo.f_go('A') where left(col,1) = 'A' and right(col,1) = 'E') a


/*

代码1 代码2 val
---- ---- -----------
A E 36


*/

drop table testt
drop function f_go

[子陌红尘]

create table BOM(CODE1 varchar(4),CODE2 varchar(4),[CODE1/CODE2] int)
insert into BOM select 'A','B',2
insert into BOM select 'B','C',3
insert into BOM select 'B','D',2
insert into BOM select 'C','E',6
go


create function F_GetQuotient(@Code1 varchar(4),@Code2 varchar(4))
returns int
as
begin
declare @Quotient int
set @Quotient=1

while (@Code1!=@Code2)
begin
select
@Quotient=@Quotient*[CODE1/CODE2],
@Code2=CODE1
from BOM where CODE2=@Code2

if @@rowcount=0
return null --非父子关系，返回空值
end

return @Quotient
end
go

select dbo.F_GetQuotient('A','E') as [A/E]

/*
A/E
-----------
36
*/


drop function F_GetQuotient
drop table BOM

[itblog]

mark

[vovo2000]

到此一游！^^；

[sxdoujg]

up