34,591
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE [dbo].[boms] (
piID varchar(20), --父ID
picID varchar(20) --子ID
)
GO
insert into boms(piID,picID) values('001','0011')
insert into boms(piID,picID) values('001','0012')
insert into boms(piID,picID) values('0012','0013')
insert into boms(piID,picID) values('0012','0014')
insert into boms(piID,picID) values('0013','00133')
insert into boms(piID,picID) values('0013','00134')
insert into boms(piID,picID) values('00133','001331')
insert into boms(piID,picID) values('00133','001332')
go
--通过以下函数可以实现查询下级的编码:
create function f_getP(@piID varchar(20))
returns @re table(picID varchar(20),lev int,piID varchar(20),TFlag varchar(10))
as
begin
declare @l int
set @l=0
insert @re select picID,@l,piID,'本级ID' from [boms] where piID = @piID
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.picID,@l,a.piID,'下级ID'
from [boms] a,@re b
where a.piID=b.picID and b.lev=@l-1 and a.picID is not null
end
update @re set lev=@l-lev
return
end
go
declare @piID varchar(10)
set @piID = '001'
select * from dbo.f_getP(@piID)