--生成测试数据
create table BOM_A(A_FATHER varchar(10),A_SUN varchar(10),A_USAGE int)
create table BOM_B(B_FATHER varchar(10),B_SUN varchar(10),B_USAGE int,B_LEVL int)
insert into BOM_A select 'a001','b001',1
insert into BOM_A select 'a001','b002',1
insert into BOM_A select 'a001','b003',1
insert into BOM_A select 'b001','c001',1
insert into BOM_A select 'b001','c002',1
insert into BOM_A select 'b001','c003',1
insert into BOM_A select 'b002','c004',1
insert into BOM_A select 'b002','c005',1
insert into BOM_A select 'c001','d001',1
insert into BOM_A select 'c001','d002',1
insert into BOM_A select 'c001','d003',1
go--创建用户定义函数,用于获取当前节点的Level
create function f_getLevel(@A_FATHER varchar(10),@A_SUN varchar(10))
returns int
as
begin
declare @i int
set @i=1
while exists(select 1 from BOM_A where A_SUN=@A_FATHER)
begin
select @A_SUN=A_FATHER,@i=@i+1 from BOM_A where A_SUN=@A_FATHER
set @A_FATHER=@A_SUN
end
return @i
end
go--执行对BOM_B表的数据插入操作
insert into BOM_B
select *,dbo.f_getLevel(A_FATHER,A_SUN) from BOM_A --查看执行结果
select * from BOM_B/*
B_FATHER B_SUN B_USAGE B_LEVL
-------- ----- ------- ------
a001 b001 1 1
a001 b002 1 1
a001 b003 1 1
b001 c001 1 2
b001 c002 1 2
b001 c003 1 2
b002 c004 1 2
b002 c005 1 2
c001 d001 1 3
c001 d002 1 3
c001 d003 1 3
*/
--删除测试数据
drop function f_getLevel
drop table BOM_A,BOM_B
create table BOM_A(A_FATHER varchar(10),A_SUN varchar(10),A_USAGE int)
create table BOM_B(B_FATHER varchar(10),B_SUN varchar(10),B_USAGE int,B_LEVL int)
insert into BOM_A select 'a001','b001',1
insert into BOM_A select 'a001','b002',1
insert into BOM_A select 'a001','b003',1
insert into BOM_A select 'b001','c001',1
insert into BOM_A select 'b001','c002',1
insert into BOM_A select 'b001','c003',1
insert into BOM_A select 'b002','c004',1
insert into BOM_A select 'b002','c005',1
insert into BOM_A select 'c001','d001',1
insert into BOM_A select 'c001','d002',1
insert into BOM_A select 'c001','d003',1
go--创建用户定义函数,用于获取当前节点的Level
create function f_getLevel(@A_FATHER varchar(10),@A_SUN varchar(10))
returns int
as
begin
declare @i int
set @i=1
while exists(select 1 from BOM_A where A_SUN=@A_FATHER)
begin
select @A_SUN=A_FATHER,@i=@i+1 from BOM_A where A_SUN=@A_FATHER
set @A_FATHER=@A_SUN
end
return @i
end
go--执行对BOM_B表的数据插入操作
insert into BOM_B
select *,dbo.f_getLevel(A_FATHER,A_SUN) from BOM_A --查看执行结果
select * from BOM_B/*
B_FATHER B_SUN B_USAGE B_LEVL
-------- ----- ------- ------
a001 b001 1 1
a001 b002 1 1
a001 b003 1 1
b001 c001 1 2
b001 c002 1 2
b001 c003 1 2
b002 c004 1 2
b002 c005 1 2
c001 d001 1 3
c001 d002 1 3
c001 d003 1 3
*/
--删除测试数据
drop function f_getLevel
drop table BOM_A,BOM_B
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货