--写个自定义函数
--查询指定id的所有子
create function f_cid(
@id int
)returns @re table(userid 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.userid,@l
from Nuser a,@re b
where a.mangername=b.userid and b.[level]=@l-1
end
return
end
go--调用(查询所有的子)
select a.*,层次=b.[level] from Nuser a,f_cid(2)b --查询 userid=2 的所有下级
where a.userid=b.userid
--查询指定id的所有子
create function f_cid(
@id int
)returns @re table(userid 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.userid,@l
from Nuser a,@re b
where a.mangername=b.userid and b.[level]=@l-1
end
return
end
go--调用(查询所有的子)
select a.*,层次=b.[level] from Nuser a,f_cid(2)b --查询 userid=2 的所有下级
where a.userid=b.userid
解决方案 »
- 表帮把临时表改写用表变量。
- 如何从数据库中读取image字段
- 求sql语句
- 怎么用Sql server2008 express打开sql server2005建的数据库
- reporting server如何显示raiserror中的信息
- SQL中对一列取唯一值并计数怎样实现?
- 数据库数据转移的问题!!
- 求救!又发现了SQL Server的一个百思不得其难的问题
- text字段如何转换成image字段??
- 我已经第三次提问了,真的这么难么?
- 请问更改一个表的某列为unique约束用sql语句怎么写,谢谢!!
- 请各位帮帮忙,SQL打了SP3的补丁之后,不能运行Sqlmangr.exe文件了,急...
我的数据
create table tuzhi(
Tz_id bigint primary key,
Tzs_id bigint,
Tz_name varchar(50))
insert int tuzhi values(1,0,'张3')
go
insert int tuzhi values(2,0,'张13')
go
insert int tuzhi values(5,2,'张23')
go
insert int tuzhi values(8,0,'张33')
go
insert int tuzhi values(10,5,'张123')
go
insert int tuzhi values(15,5,'张873')
go
insert int tuzhi values(16,0,'张783')
go
insert int tuzhi values(17,10,'张453')
go
insert int tuzhi values(18,15,'张4563')
go
insert int tuzhi values(19,15,'张453')
go
insert int tuzhi values(20,10,'张22')
go
insert int tuzhi values(21,10,'张222')
go
insert int tuzhi values(22,2,'张89')
go
insert int tuzhi values(23,22,'张35')
go
insert int tuzhi values(24,22,'张97')
go
其中 用户id Tz_id ,用户名 Tz_name,经理id Tzs_id
我把你的方法改成
--写个自定义函数
--查询指定id的所有子
drop function f_cid
go
create function f_cid(
@id int
)returns @re table(Tz_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.Tz_id,@l
from tuzhi a,@re b
where a.Tzs_id=b.Tz_id and b.[level]=@l-1
end
return
end
go--调用(查询所有的子)
select a.*,层次=b.[level] from tuzhi a,f_cid(2)b --查询 Tz_id=2 的所有下级
where a.Tz_id=b.Tz_id
不好用
create table [Nuser](userid bigint,mangername bigint,name nvarchar(20))
insert [Nuser] select 1,0,'aa'
union all select 2,0,'bb'
union all select 3,0,'cc'
union all select 4,1,'dd'
union all select 5,1,'ee'
union all select 6,1,'ff'
union all select 7,6,'gg'
union all select 8,7,'hh'
union all select 9,6,'ii'
go--自定义函数
--查询指定id的所有子
create function f_cid(
@id int
)returns @re table(userid 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.userid,@l
from Nuser a,@re b
where a.mangername=b.userid and b.[level]=@l-1
end
delete a from @re a
where exists(
select 1 from Nuser where mangername=a.userid)
return
end
go--调用(查询所有的子)
select a.*,层次=b.[level] from Nuser a,f_cid(1)b --查询 userid=1 的所有员工
where a.userid=b.userid
go--删除测试
drop table Nuser
drop function f_cid/*--测试结果userid mangername name 层次
-------------------- -------------------- -------------------- -----------
4 1 dd 1
5 1 ee 1
8 7 hh 3
9 6 ii 2(所影响的行数为 4 行)
--*/
create table tuzhi(
Tz_id bigint primary key,
Tzs_id bigint,
Tz_name varchar(50))
insert into tuzhi values(1,0,'张3')
insert into tuzhi values(2,0,'张13')
insert into tuzhi values(5,2,'张23')
insert into tuzhi values(8,0,'张33')
insert into tuzhi values(10,5,'张123')
insert into tuzhi values(15,5,'张873')
insert into tuzhi values(16,0,'张783')
insert into tuzhi values(17,10,'张453')
insert into tuzhi values(18,15,'张4563')
insert into tuzhi values(19,15,'张453')
insert into tuzhi values(20,10,'张22')
insert into tuzhi values(21,10,'张222')
insert into tuzhi values(22,2,'张89')
insert into tuzhi values(23,22,'张35')
insert into tuzhi values(24,22,'张97')
go--自定义函数
--查询指定id的所有子
create function f_cid(
@id int
)returns @re table(Tz_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.Tz_id,@l
from tuzhi a,@re b
where a.Tzs_id=b.Tz_id and b.[level]=@l-1
end
delete a from @re a
where exists(
select 1 from tuzhi where Tzs_id=a.Tz_id)
return
end
go--调用(查询所有的子)
select a.*,层次=b.[level] from tuzhi a,f_cid(2)b --查询 Tz_id=2 的所有下级
where a.Tz_id=b.Tz_id
go--删除测试
drop table tuzhi
drop function f_cid/*--测试结果Tz_id Tzs_id Tz_name 层次
------- ------- ---------- ----
23 22 张35 2
24 22 张97 2
17 10 张453 3
20 10 张22 3
21 10 张222 3
18 15 张4563 3
19 15 张453 3(所影响的行数为 7 行)
--*/
delete a from @re a
where exists(
select 1 from tuzhi where Tzs_id=a.Tz_id)