sql 语句怎么写!

[lslyl]

branch_ID branch_name branch_level ralation_ID user_show
1 中国航 1 0 Y
2 出口部 2 1 Y
3 合作部 2 1 Y
4 综合处 3 2 Y
5 亚洲处 3 2 Y
6 售后处 3 2 Y
7 出口财 3 2 Y
8 综管处 3 3 Y
9 下下级 4 8 Y

[lslyl]

是这样的,我想用一个sql语句把上面表某一级下的把有部门都检索出来.怎么写

[qizhanfeng]

up

[davorsuker39]

select branch_name from table where branch_level=(数字) --你是这个意思吗？

[davorsuker39]

select branch_name from table where branch_level=2

结果：

出口部
合作部

[lslyl]

他的所有下级都出来.用branch_id检索.如果branch_id=2那么 综合处 亚洲处 售后处 出口财 综管处 下下级都出来.

[pjy]

如果单位级别是个固定的层数，比如4级、5级 还可以用一个语句写出来，
如果不固定，可以任意级别，一条语句，那我写不出来。

[passionke]

给个例子：
--------------------------------------------
create table DINOSAURS
(
OrderNo int primary KEY,
OrderName varchar(30),
PredecessorNo int NULL REFERENCES DINOSAURS (OrderNo)
)

insert into DINOSAURS values(1,'中国',1)
insert into DINOSAURS values(2,'河南省',1)
insert into DINOSAURS values(3,'郑州',2)
insert into DINOSAURS values(4,'驻马店',2)
insert into DINOSAURS values(5,'管城区',3)
insert into DINOSAURS values(6,'中原区',3)
insert into DINOSAURS values(7,'二七区',3)
insert into DINOSAURS values(8,'芒山区',3)
insert into DINOSAURS values(9,'金水区',3)
insert into DINOSAURS values(10,'经八路',9)
insert into DINOSAURS values(11,'红旗路',9)
insert into DINOSAURS values(12,'文化路',9)
insert into DINOSAURS values(13,'黄河路',9)
------------------------------------------------
创建存储过程：
---------------------------------------------

CREATE PROCEDURE dbo.treeFrame AS

create table #work(lvl int, OrderNo int)
create table #DINOSAURS(seq int identity, lvl int, OrderNo int)

declare @lvl int,@curr int
select top 1 @lvl = 1,@curr = OrderNo from DINOSAURS where OrderNo = PredecessorNo

insert into #work (lvl, OrderNo) values(@lvl, @curr)
while(@lvl > 0) begin
print @lvl
if exists(select * from #work where lvl = @lvl) begin
select top 1 @curr = OrderNo from #work where lvl = @lvl

insert into #DINOSAURS(lvl, OrderNo) values(@lvl, @curr)

delete #work where lvl = @lvl and OrderNo = @curr

insert into #work select @lvl + 1, OrderNo from DINOSAURS
where PredecessorNo = @curr and PredecessorNo <> OrderNo

if(@@ROWCOUNT > 0) set @lvl = @lvl + 1
end else
set @lvl = @lvl - 1
end

select i.OrderNo as OrderNo,REPLICATE(CHAR(9),d.lvl) + i.OrderName as OrderName, d.lvl as depth
from #DINOSAURS d join DINOSAURS i on (d.OrderNo = i.OrderNo)
order by seq
----------------------------------------------------------
执行结果：exec treeFrame
--------------------------------
OrederNo OrderName depth
1 中国 1
2 河南省 2
3 郑州 3
5 管城区 4
6 中原区 4
7 二七区 4
8 芒山区 4
9 金水区 4
10 经八路 5
11 红旗路 5
12 文化路 5
13 黄河路 5
4 驻马店 3

[lslyl]

固定怎么写

[mgsray]

写个函数,参考以下
select * from tb
create table tb(id int identity(1,1),pid int,name varchar(20))
insert tb select 0,'中国'
union all select 0,'美国'
union all select 0,'加拿大'
union all select 1,'北京'
union all select 1,'上海'
union all select 1,'江苏'
union all select 6,'苏州'
union all select 7,'常熟'
union all select 6,'南京'
union all select 6,'无锡'
union all select 2,'纽约'
union all select 2,'旧金山'
go

--查询指定id的所有子
create function f_cid(
@id int
)returns @re table(id int,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 tb a,@re b
where a.pid=b.id and b.level=@l-1
end
/*--如果只显示最明细的子(下面没有子),则加上这个删除
delete a from @re a
where exists(
select 1 from tb where pid=a.id)
--*/
return
end
go

--调用(查询所有的子)
select a.*,层次=b.level from tb a,f_cid(2)b where a.id=b.id
go

--查询指定id的所有父
create function f_pid(
@id int
)returns @re table(id int,level int)
as
begin
declare @l int
set @l=0
insert @re select pid,@l from tb where id=@id and pid<>0
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.pid,@l
from tb a,@re b
where a.id=b.id and b.level=@l-1 and a.pid<>0
end
return
end
go

--调用(查询所有的父)
select a.* from tb a,f_pid(7)b where a.id=b.id
go

--删除测试
drop table tb
drop function f_cid
drop function f_pid

[davorsuker39]

哦,是这个意思呀,那么你用楼上的树型结构好啦