34,838
社区成员




create table #tb(pid varchar(10), cid varchar(10))
insert into #tb values('A' , 'A1')
insert into #tb values('A1' , 'A11')
insert into #tb values('A1' , 'A12')
insert into #tb values('A12' , 'A121')
insert into #tb values('A' , 'A2')
insert into #tb values('B' , 'B1')
insert into #tb values('B1' , 'B11')
insert into #tb values('B1' , 'B12')
insert into #tb values('B' , 'B2')
go
select substring(pid,1,1) as 顶层零件,pid as 父零件代码,cid as 零件代码,层次=len(pid) from #tb
drop table #tb
/*
A A A1 1
A A1 A11 2
A A1 A12 2
A A12 A121 3
A A A2 1
B B B1 1
B B1 B11 2
B B1 B12 2
B B B2 1
*/
create table tb(pid varchar(10), cid varchar(10))
insert into tb values('A' , 'A1')
insert into tb values('A1' , 'A11')
insert into tb values('A1' , 'A12')
insert into tb values('A12' , 'A121')
insert into tb values('A' , 'A2')
insert into tb values('B' , 'B1')
insert into tb values('B1' , 'B11')
insert into tb values('B1' , 'B12')
insert into tb values('B' , 'B2')
go
create function f_pid(@cid varchar(10)) returns varchar(20)
as
begin
declare @re_str as varchar(10)
set @re_str = ''
declare @level as int
set @level = 0
select @re_str = pid from tb where cid = @cid
while exists (select 1 from tb where cid = @re_str)
begin
select @re_str = pid from tb where cid = @re_str
set @level = @level + 1
end
return @re_str + ',' + cast(@level + 1 as varchar)
end
go
select 顶层零件 = left(dbo.f_pid(cid),charindex(',',dbo.f_pid(cid))-1) ,
父零件代码 = pid ,
零件代码 = cid ,
substring(dbo.f_pid(cid),charindex(',',dbo.f_pid(cid))+1 , len(dbo.f_pid(cid))) 层次 from tb
drop table tb
drop function dbo.f_pid
/*
顶层零件 父零件代码 零件代码 层次
-------------------- ---------- ---------- --------------------
A A A1 1
A A1 A11 2
A A1 A12 2
A A12 A121 3
A A A2 1
B B B1 1
B B1 B11 2
B B1 B12 2
B B B2 1
(所影响的行数为 9 行)
*/
create table tb(pid varchar(10), cid varchar(10))
insert into tb values('A' , 'A1')
insert into tb values('A1' , 'A11')
insert into tb values('A1' , 'A12')
insert into tb values('A12' , 'A121')
insert into tb values('A' , 'A2')
insert into tb values('B' , 'B1')
insert into tb values('B1' , 'B11')
insert into tb values('B1' , 'B12')
insert into tb values('B' , 'B2')
go
create function f_pid(@cid varchar(10)) returns int
as
begin
declare @re_str as varchar(10)
set @re_str = ''
declare @level as int
set @level = 0
select @re_str = pid from tb where cid = @cid
while exists (select 1 from tb where cid = @re_str)
begin
select @re_str = pid from tb where cid = @re_str
set @level = @level + 1
end
return @level + 1
end
go
select * , dbo.f_pid(cid) 层次 from tb
drop table tb
drop function dbo.f_pid
/*
pid cid 层次
---------- ---------- -----------
A A1 1
A1 A11 2
A1 A12 2
A12 A121 3
A A2 1
B B1 1
B1 B11 2
B1 B12 2
B B2 1
(所影响的行数为 9 行)
*/
--生成测试数据
create table BOM(ID INT,PID INT,MSG VARCHAR(1000))
insert into BOM select 1,0,NULL
insert into BOM select 2,1,NULL
insert into BOM select 3,1,NULL
insert into BOM select 4,2,NULL
insert into BOM select 5,3,NULL
insert into BOM select 6,5,NULL
insert into BOM select 7,6,NULL
go
--创建用户定义函数用于取每个父节点下子节点的采购配置信息
create function f_getChild(@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)
as
begin
declare @i int
set @i = 1
insert into @t select ID,PID,@i from BOM where PID = @ID
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.ID,a.PID,@i
from
BOM a,@t b
where
a.PID=b.ID and b.Level = @i-1
end
return
end
go
--执行查询
select ID from dbo.f_getChild(3)
go
--输出结果
/*
ID
----
5
6
7
*/
--删除测试数据
drop function f_getChild
drop table BOM
创建用户定义函数,每个子节点de父节点的信息
--生成测试数据
create table BOM(ID int,parentID int,sClassName varchar(10))
insert into BOM values(1,0,'1111' )
insert into BOM values(2,1,'1111_1' )
insert into BOM values(3,2,'1111-1-1' )
insert into BOM values(4,3,'1111-1-1-1')
insert into BOM values(5,1,'1111-2' )
go
--创建用户定义函数,每个子节点de父节点的信息
create function f_getParent(@ID int)
returns varchar(40)
as
begin
declare @ret varchar(40)
while exists(select 1 from BOM where ID=@ID and parentID<>0)
begin
select @ID=b.ID,@ret=','+rtrim(b.ID)+isnull(@ret,'')
from
BOM a,BOM b
where
a.ID=@ID and b.ID=a.parentID
end
set @ret=stuff(@ret,1,1,'')
return @ret
end
go
--执行查询
select ID,isnull(dbo.f_getParent(ID),'') as parentID from BOM
go
--输出结果
/*
ID parentID
----------- ----------------------------------------
1
2 1
3 1,2
4 1,2,3
5 1
*/
--删除测试数据
drop function f_getParent
drop table BOM
go