34,838
社区成员




corpid deptid parentid
--------------
--创建用户定义函数用于取每个父节点下子节点的信息
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 deptid,parentid,@i from ta where deptid= @ID
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.deptid,a.parentid,@i
from
BOM a,@t b
where
a.parentid=b.ID and b.Level = @i-1
end
return
end
go
corpid deptid parentid
--------------
--创建用户定义函数用于取每个父节点下子节点的信息
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 ta where deptid= @ID
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.deptid,a.parentid,@i
from
BOM a,@t b
where
a.parentid=b.ID and b.Level = @i-1
end
return
end
go
select *
from ta
where (corpid = 'xx' and deptid = 'A1') or parentid = 'A1'
select *
from ta
where corpid = 'xx' and deptid = 'A1'
--生成测试数据
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
如何执行一条SQL语句把corpid为'xx'的所有部门都列出来?
--
select *
from ta
where corpid = 'xx'