--楼主别忙着该数据库啊.create table table1 ( 部门ID varchar(2) , 部门名称 varchar(40) , 上级部门ID varchar(2) ) insert into table1 select '1' , '河南省' , '0' union all select '2' , '湖南省' , '0' union all select '3' , '南阳市' , '1' union all select '4' , '郑州市' , '1' union all select '5' , 'AAA县' , '3' union all select '6' , 'BBB县' , '3' union all select '7' , 'CCC县' , '3' union all select '8' , 'DDD县' , '3'create function f_cccid( @id varchar(2) )returns @re table([部门ID] varchar(2),[level] int) as begin declare @l int set @l=0 insert @re select @id,@l while @@rowcount>0 begin set @l=@l+1 insert @re select a.[部门ID],@l from [table1] a,@re b where a.[上级部门ID]=b.[部门ID] and b.[level]=@l-1 end return end go select * from table1 where 部门ID in ( select 部门ID from dbo.f_cccid('1')) order by 部门ID-------------------------------------------------------------- 部门ID 部门名称 上级部门ID 1 河南省 0 3 南阳市 1 4 郑州市 1 5 AAA县 3 6 BBB县 3 7 CCC县 3 8 DDD县 3(所影响的行数为 7 行)
create table table1 ( 部门ID varchar(2) , 部门名称 varchar(40) , 上级部门ID varchar(2) ) insert into table1 select '1' , '河南省' , '0' union all select '2' , '湖南省' , '0' union all select '3' , '南阳市' , '1' union all select '4' , '郑州市' , '1' union all select '5' , 'AAA县' , '3' union all select '6' , 'BBB县' , '3' union all select '7' , 'CCC县' , '3' union all select '8' , 'DDD县' , '3'create function f_cccid( @id varchar(2) )returns @re table([部门ID] varchar(2),[level] int) as begin declare @l int set @l=0 insert @re select @id,@l while @@rowcount>0 begin set @l=@l+1 insert @re select a.[部门ID],@l from [table1] a,@re b where a.[上级部门ID]=b.[部门ID] and b.[level]=@l-1 end return end go select * from table1 where 部门ID in ( select 部门ID from dbo.f_cccid('1')) order by 部门ID-------------------------------------------------------------- 部门ID 部门名称 上级部门ID 1 河南省 0 3 南阳市 1 4 郑州市 1 5 AAA县 3 6 BBB县 3 7 CCC县 3 8 DDD县 3
类似BOM树的生成算法 伪码如下: declare tempresult @table(...) --结果Dept树 while exists(select * from .. ) begin insert tempresult(...) values (...) --当while条件不满足或者@@rowcount=0 break end
如果有多级:恐怕只能用游标或在程序中递归了。
你的要求是查询上级部门下级部门也要出来。如果你的部门ID这样设置的话那要用到比较长的IF语句建议你把部门ID改为2进制的。
部门ID 部门名称 上级部门ID
1 河南省 0
2 湖南省 0
101 南阳市 1
102 郑州市 1
10101 AAA县 101
10102 BBB县 101
10103 CCC县 101
10104 DDD县 101
这样就比较好查询了。
(
部门ID varchar(2) , 部门名称 varchar(40) , 上级部门ID varchar(2)
)
insert into table1
select '1' , '河南省' , '0' union all
select '2' , '湖南省' , '0' union all
select '3' , '南阳市' , '1' union all
select '4' , '郑州市' , '1' union all
select '5' , 'AAA县' , '3' union all
select '6' , 'BBB县' , '3' union all
select '7' , 'CCC县' , '3' union all
select '8' , 'DDD县' , '3'create function f_cccid(
@id varchar(2)
)returns @re table([部门ID] varchar(2),[level] int)
as
begin
declare @l int
set @l=0
insert @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.[部门ID],@l
from [table1] a,@re b
where a.[上级部门ID]=b.[部门ID] and b.[level]=@l-1
end
return
end
go
select * from table1 where 部门ID in (
select 部门ID from dbo.f_cccid('1'))
order by 部门ID--------------------------------------------------------------
部门ID 部门名称 上级部门ID
1 河南省 0
3 南阳市 1
4 郑州市 1
5 AAA县 3
6 BBB县 3
7 CCC县 3
8 DDD县 3(所影响的行数为 7 行)
(
部门ID varchar(2) , 部门名称 varchar(40) , 上级部门ID varchar(2)
)
insert into table1
select '1' , '河南省' , '0' union all
select '2' , '湖南省' , '0' union all
select '3' , '南阳市' , '1' union all
select '4' , '郑州市' , '1' union all
select '5' , 'AAA县' , '3' union all
select '6' , 'BBB县' , '3' union all
select '7' , 'CCC县' , '3' union all
select '8' , 'DDD县' , '3'create function f_cccid(
@id varchar(2)
)returns @re table([部门ID] varchar(2),[level] int)
as
begin
declare @l int
set @l=0
insert @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.[部门ID],@l
from [table1] a,@re b
where a.[上级部门ID]=b.[部门ID] and b.[level]=@l-1
end
return
end
go
select * from table1 where 部门ID in (
select 部门ID from dbo.f_cccid('1'))
order by 部门ID--------------------------------------------------------------
部门ID 部门名称 上级部门ID
1 河南省 0
3 南阳市 1
4 郑州市 1
5 AAA县 3
6 BBB县 3
7 CCC县 3
8 DDD县 3
伪码如下:
declare tempresult @table(...) --结果Dept树
while exists(select * from .. )
begin
insert tempresult(...) values (...)
--当while条件不满足或者@@rowcount=0 break
end