我这里有一张公司结构的表,表结构如下:
corpid varchar(20) 公司ID
deptid varchar(20) 部门ID
parentid varchar(20) 上级部门ID(没有上级部门为-1)内容如下:
corpid deptid parentid
------------------------------------------
xx A -1
xx A1 A
xx A2 A
xx A3 A
xx A11 A1
xx A12 A1
xx A111 A11
xx A112 A11
xx A31 A3
xx A32 A3
yy A -1
yy B A
.
.
.
如何执行一条SQL语句把corpid为'xx'的所有部门都列出来?
corpid varchar(20) 公司ID
deptid varchar(20) 部门ID
parentid varchar(20) 上级部门ID(没有上级部门为-1)内容如下:
corpid deptid parentid
------------------------------------------
xx A -1
xx A1 A
xx A2 A
xx A3 A
xx A11 A1
xx A12 A1
xx A111 A11
xx A112 A11
xx A31 A3
xx A32 A3
yy A -1
yy B A
.
.
.
如何执行一条SQL语句把corpid为'xx'的所有部门都列出来?
select deptid from [thetable] where corpid='xx'可能这不是你要的输出效果,但内容应该没问题。
--
select *
from ta
where corpid = 'xx'子节点要不要?
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
呵呵,是啊, 那也太简单了.
select *
from ta
where corpid = 'xx' and deptid = 'A1'
不用写如何创建表,有表结构手工创建就可以了.谢谢大家踊跃发言!
--or
select *
from ta
where (corpid = 'xx' and deptid = 'A1') or parentid = 'A1'
--创建用户定义函数用于取每个父节点下子节点的信息
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
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
from ta
where corpid = 'xx'
and ( deptid ='A1' or parentid ='A1')
---可创建一个函数来取得xx 部门为A1 的所有下属部门呀
你不是要取得 公司xx 部门为A1 的所有下属部门呀?
我那个函数可以满足你的要求呀