create procedure sp_getchild(@auto_id int)
as
begin
declare @i int
declare @r varchar(8000)
set @i = 1
set @r = ''
select @i as level,* into #t from 表 where parent_id = @auto_id
while exists(select 1 from 表 A,#t B where A.parent_id = B.auto_id and B.level = @i)
begin
select @i+1,A.* into #t from 表 A,#t B where A.parent_id = B.auto_id and B.level = @i
set @i = @i + 1
end
select @r = @r + ','+rtrim(auto_id) from #t order by auto_id
return stuff(@r+' ',1,1,'')
end
as
begin
declare @i int
declare @r varchar(8000)
set @i = 1
set @r = ''
select @i as level,* into #t from 表 where parent_id = @auto_id
while exists(select 1 from 表 A,#t B where A.parent_id = B.auto_id and B.level = @i)
begin
select @i+1,A.* into #t from 表 A,#t B where A.parent_id = B.auto_id and B.level = @i
set @i = @i + 1
end
select @r = @r + ','+rtrim(auto_id) from #t order by auto_id
return stuff(@r+' ',1,1,'')
end
解决方案 »
- 求一SQL语句
- 分区表的问题!
- 以sa的身份打开syscomments表,删除text字段中的一个值,但无法删除,提示“无法编辑该单元”,什么原因?
- 急,事物同步复制,UNC目录下N多文件
- 从第一个表中取出某一字段的值作为第二个select。。from。所要用到的表(要求遍历第一个表中该字段的所有值)
- select substring(tName,0,charindex('1',tName,1))+cast(right(tName,1)+1 as va
- sql数据库安全问题
- 很菜的问题来看一下
- 请教高人:请过来帮忙看看,维护大容量数据库的问题?
- 一个SQL语句问题
- 高手请指点一下,两表修改问题“?????
- 一个case函数小问题,在线等待?????
create table t (
auto_id int,
name varchar(10),
parent_id int,
havesonflag int)insert into t select 1,'总公司 ',0,1
insert into t select 2,'子公司1',1,1
insert into t select 3,'子公司2',1,1
insert into t select 4,'技术部 ',2,0
insert into t select 5,'销售部 ',2,0
insert into t select 6,'生产部 ',3,1
insert into t select 7,'车间1 ',6,0
insert into t select 8,'车间2 ',6,0
insert into t select 9,'加工部 ',3,0
--创建存储过程
create procedure sp_getchild(@auto_id int)
as
begin
declare @i int
declare @r varchar(8000)
set @i = 1
set @r = ''
select @i as level,* into #t from t where parent_id = @auto_id
while exists(select 1 from t A,#t B where A.parent_id = B.auto_id and B.level = @i)
begin
insert into #t select @i+1,A.* from t A,#t B where A.parent_id = B.auto_id and B.level = @i
set @i = @i + 1
end
select @r = @r + ','+rtrim(auto_id) from #t order by auto_id select stuff(@r + ' ',1,1,'')
end
--执行存储过程
exec sp_getchild 2
能给重要点地方都加注释吗?我真的看不懂
能给重要点地方都加注释吗?我真的看不懂
能给重要点地方都加注释吗?我真的看不懂
能给重要点地方都加注释吗?我真的看不懂
能给重要点地方都加注释吗?我真的看不懂
能给重要点地方都加注释吗?我真的看不懂
能给重要点地方都加注释吗?我真的看不懂
能给重要点地方都加注释吗?我真的看不懂
能给重要点地方都加注释吗?我真的看不懂
Create table tableTree (
auto_id Int identity(1,1),
name Nvarchar(50),
parent_id Int,
havesonflag Bit)--插入数据
Insert tableTree values(N'总公司',0,1)
Insert tableTree values(N'子公司1',1,1)
Insert tableTree values(N'子公司2',1,1)
Insert tableTree values(N'技术部',2,0)
Insert tableTree values(N'销售部',2,0)
Insert tableTree values(N'生产部',3,1)
Insert tableTree values(N'车间1',6,0)
Insert tableTree values(N'车间2',6,0)
Insert tableTree values(N'加工部',3,0)--建存储过程
Create Procedure DisplayTree
@parent_id int ,
@ChildID Nvarchar(20) Output
AS
Begin
declare @ID varchar(8000)
set @ID =''
select @ID = @ID + ','+rtrim(auto_id) from tableTree Where parent_id=@parent_id order by auto_id
set @ChildID=Stuff(@ID+' ',1,1,'')
EndGO--测试Declare @ChildID Nvarchar(20)
exec DisplayTree 1,@ChildID Output
Select '子节点'=@ChildID--结果
2,3
create procedure sp_getchild(@auto_id int)
as
begin
declare @i int
declare @r varchar(8000)
set @i = 1
set @r = ''
--将当前传入的节点的第一层字节点数据插入到临时表#t中
--并将其节点层次设置为@i
select @i as level,* into #t from t where parent_id = @auto_id
--当临时表#t中当前最深一级节点仍然有字节点存在,则将其字节点插入到临时表中
--并且将新插入临时表的节点层次设置为@i+1
--注意,此处就是递归
while exists(select 1 from t A,#t B where A.parent_id = B.auto_id and B.level = @i)
begin
insert into #t select @i+1,A.* from t A,#t B where A.parent_id = B.auto_id and B.level = @i
set @i = @i + 1
end
--通过SQL语句自身的循环将临时表中所有的节点编号生成一个大字符串
select @r = @r + ','+rtrim(auto_id) from #t order by auto_id
--返回拼装好的字符串,并且删除最前面的","符号
select stuff(@r + ' ',1,1,'')
end
insert into test select '总公司',0,1 union all
select '子公司1',1,1 union all select '子公司2',1,1 union all
select '技术部',2,0 union all select '销售部',2,0 union all
select '生产部',3,1 union all select '车间1',6,1 union all
select '车间1',6,0 union all select '加工部',3,0 select * from testcreate proc t
@id int
--@s nvarchar(100) output
as
select * into #t from test
declare @tid int
declare @tss nvarchar(100)
declare @ts nvarchar(100)
set @ts=''
select @ts=@ts+quotename(cast(auto_id as nvarchar(10)),'''')+',' from #t where parent_id=@id
set @tss=@ts
delete from #t where parent_id=@id
while exists(select * from #t where charindex(quotename(cast(parent_id as nvarchar(10)),''''),@ts)>0)
begin
select @ts=@ts+quotename(cast(auto_id as nvarchar(10)),'''')+',' from #t where charindex(quotename(cast(parent_id as nvarchar(10)),''''),@ts)>0
delete from #t where charindex(quotename(cast(parent_id as nvarchar(10)),''''),@tss)>0
set @tss=@ts
end
set @ts=quotename(cast(@id as nvarchar(10)),'''')+','+@ts
set @ts=replace(@ts,'''','')
select @ts
goexec t 2drop proc t
drop table test
go
drop table ##table
go
create table ##table
(
autoid int not null,
name nvarchar(20),
parent_id int,
havesonflag bit
)
insert into ##table
select 1, '总公司', 0 , 1 union
select 2, '子公司1', 1 , 1 union
select 3, '子公司', 1 , 1 union
select 4, '技术部', 2 , 0 union
select 5, '销售部', 2 , 0 union
select 6, '生产部', 3 , 1 union
select 7, '车间1' , 6 , 0 union
select 8, '车间2' , 6 , 0 union
select 9, '加工部', 3 , 0
go
alter table ##table add path varchar(200)go
while @@rowcount>0
begin
update c
set c.path=case
when c.parent_id=0 then '>0>'
else (select a.path+rtrim(b.parent_id)+'>' from ##table a,##table b where b.parent_id=a.autoid and b.autoid=c.autoid) end
from ##table c where exists (select path from ##table where path is null)
endgo
select * from ##table
select autoid from ##table where charindex('>2>',path)>0 --返回2下的所有子结点
insert tb select 1,'总公司' ,0,1
union all select 2,'子公司1',1,1
union all select 3,'子公司2',1,1
union all select 4,'技术部' ,2,0
union all select 5,'销售部' ,2,0
union all select 6,'生产部' ,3,1
union all select 7,'车间1' ,6,0
union all select 8,'车间2' ,6,0
union all select 9,'加工部' ,3,0
go--存储过程
create proc p_qry
@id int
as
set nocount on
create table #t(id int,level int)
declare @l int
set @l=0
insert #t select auto_id,@l from tb where parent_id=@id
while @@rowcount>0
begin
set @l=@l+1
insert #t select a.auto_id,@l
from tb a,#t b
where a.parent_id=b.id and b.level=@l-1
end
select a.*
from tb a,#t b where a.auto_id=b.id
go--调用实现查询
exec p_qry 1
exec p_qry 2
go--删除测试
drop table tb
drop proc p_qry/*--测试结果auto_id name parent_id havesonflag
----------- ---------- ----------- -----------
2 子公司1 1 1
3 子公司2 1 1
4 技术部 2 0
5 销售部 2 0
6 生产部 3 1
7 车间1 6 0
8 车间2 6 0
9 加工部 3 0
auto_id name parent_id havesonflag
----------- ---------- ----------- -----------
4 技术部 2 0
5 销售部 2 0
--*/
@id int
as
begin
create table ##table
(
autoid int not null,sname nvarchar(20), parent_id int, havesonflag bit, path varchar(200)
)
insert into ##table(autoid,sname,parent_id,havesonflag)
select 1, '总公司', 0 , 1 union
select 2, '子公司1', 1 , 1 union
select 3, '子公司', 1 , 1 union
select 4, '技术部', 2 , 0 union
select 5, '销售部', 2 , 0 union
select 6, '生产部', 3 , 1 union
select 7, '车间1' , 6 , 0 union
select 8, '车间2' , 6 , 0 union
select 9, '加工部', 3 , 0 while @@rowcount>0
begin
update c
set c.path=case
when c.parent_id=0 then '>0>'
else (select a.path+rtrim(b.parent_id)+'>' from ##table a,##table b where b.parent_id=a.autoid and b.autoid=c.autoid) end
from ##table c where exists (select path from ##table where path is null)
endcreate table #temp(id int)
exec('insert into #temp select autoid from ##table where charindex(''>'+@id+'>'',path)>0')
select * from #tempdrop table ##table
drop table #tempend
goexec proc_test '2' --2下的所有子结点drop proc proc_test测试结果:
id
----
4
5
请问返回值在那里?是这句吗select a.* from tb a,#t b where a.auto_id=b.id
您认为完成这种功能是写函数好呢还是存储过程好呢?
前面的循环是得到指定id的所有子id的一般来说,如果这个数据不会与其他数据关联使用的话,用存储过程,'如果经常还要与其他数据关联重用的话,一般用函数当然,返回的数据量大也推荐用存储过程