create procedure p_aaaa
as
select distinct convert(char(18),id) from Client
as
select distinct convert(char(18),id) from Client
解决方案 »
- 有一段SQL代码要定期执行,咋弄啊~~
- sql xml简单的问题
- 为了使多个客户机存取访问服务器数据库不产生并发冲突问题,是否尽量少用或不用全局的记录集变量?就是避免使用以下象这样的Public变量对吗?
- 有一个字段的默认值是根据ID来变化的,怎么实现?
- 求一个数据库架构设计方案
- 请问诸位你们写存储过程时是用什么工具?
- MS SQL 2005 变量问题 急急急!!!
- 为什么在两张表合并后会出现重复的项目?
- 关于外键的讨论!
- 关于SQL Server之间的连接问题(巨难,分不够可再加)
- 如何使用 INSTEAD OF UPDATE 触发器?
- sqlserver能不能定义数组?他有没有类似VB里的 string类型数据的split方法?有该怎么用?
create table Client(id int ,parentid int)
insert into Client select 1,null
insert into Client select 2 , 1
insert into Client select 3 , 1
insert into Client select 4 , 2
insert into Client select 5 , 1
go
create proc proc_out
@str varchar(8000) output
as
select @str=''
select @str=@str+convert(varchar(100),id)+',' from client
select @str=left(@str,len(@str)-1)
godeclare @strout varchar(8000)
exec proc_out @strout output
print @strout
,(select parentid
from T
where id = a.parentid)
,(select parentid
from T
where id = (select parentid
from T
where id = a.parentid))
,(select parentid
from T
where id = ((select parentid
from T
where id = (select parentid
from T
where id = a.parentid))))
from T a
where id not in (select parentid from tree)
抛砖引玉:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[up_GetChildInfoString]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[up_GetChildInfoString]
GO
CREATE PROCEDURE up_GetChildInfoString
@m_objDB varchar(50),@m_sParentField varchar(100),@m_sParentValue numeric,@m_ReturnValue varchar(1000) output
As Declare @objDB varchar(50)
Set @objDB=@m_objDBDeclare @id_Temp numericDeclare Cur_Info scroll Cursor for Select id from business..project where ParentPrj=@m_sParentValue
open Cur_Info
Fetch Next from Cur_Info into @id_Temp
while(@@Fetch_Status=0) begin
set @m_ReturnValue=@m_ReturnValue+';'+cast(@id_Temp as varchar(1000))
print @m_ReturnValue
Fetch Next from Cur_Info into @id_Temp
end
close Cur_Info
Deallocate Cur_Info这只能查出一级的,在储过程中怎么才能查多级的,我在存储过程中不会用递归
set @=''
select @=@+','+cast(id as varchar(10)) from clientselect right(@,len(@)-1) 结果
--不包含排序字段的情况
create function f_getchildid(@id int)
returns @re table(id int)
as
begin
insert into @re select id from tb where pid=@id
while @@rowcount>0
insert into @re select a.id
from tb a inner join @re b on a.pid=b.id
where a.id not in(select id from @re)
return
end
go--包含排序字段的情况
create function f_getchildidsort(@id int)
returns @re table(id int,sortid varchar(8000))
as
begin
--为了数字排序正常,需要统一编码宽度
declare @idlen int,@idheader varchar(20)
select @idlen=max(len(id))
,@idheader=space(@idlen)
from tb insert into @re select id,right(@idheader+cast(id as varchar),@idlen)
from tb where pid=@id
while @@rowcount>0
insert into @re select a.id,right(@idheader+cast(a.id as varchar),@idlen)+','+b.sortid
from tb a inner join @re b on a.pid=b.id
where a.id not in(select id from @re)
return
end
go--调用示例,显示1的所有子.
select a.* from tb a inner join dbo.f_getchildidsort(1) b on a.id=b.id order by b.sortid
函数返回的是表集.
declare @str varchar(8000)
set @str=''
select @str=@str+','+cast(id as varchar0 from dbo.f_getchildid(1)
set @str=substring(@str,2,8000)
print @str
更多的内容参考:
http://expert.csdn.net/Expert/topic/2285/2285830.xml?temp=.1570551
http://www.csdn.net/Develop/Read_Article.asp?Id=17247
select id,parentid
,(select parentid
from Tree
where id = a.parentid)
,(select parentid
from Tree
where id = (select parentid
from Tree
where id = a.parentid ))
,(select parentid
from Tree
where id = ((select parentid
from Tree
where id = (select parentid
from Tree
where id = a.parentid ))))
,(select parentid
from Tree
where id =( (select parentid
from Tree
where id = ((select parentid
from Tree
where id = (select parentid
from Tree
where id = a.parentid )))) ))from Tree a
where id not in (select parentid from tree)一道 SQL 题 ... (关于树型结构的在关系表中的存储及其应用处理)
http://www.csdn.net/Develop/Read_Article.asp?Id=17247树型结构数据在数据库基本表中的存储及维护
http://www.csdn.net/Develop/Read_Article.asp?Id=18666