表 tbs 字段 id,nams,fatherid 1,aaaa,0
2,bbbb,1
3,cccc,2
4,dddd,3如果查询id=4 就显示 aaaa,bbbb,cccc,dddd 这个sql怎么写啊??
查询id=3 就显示aaaa,bbbb,cccc数据
2,bbbb,1
3,cccc,2
4,dddd,3如果查询id=4 就显示 aaaa,bbbb,cccc,dddd 这个sql怎么写啊??
查询id=3 就显示aaaa,bbbb,cccc数据
解决方案 »
- 请问create table 时,为何有时table前面是空的,有时是#,有时用[]呢
- 查询结果建表保存
- 数据库算法
- 在查询结果中查询的实现?
- 2表查询取唯一问题
- VS2010无法连接到SQL 2008 R2
- 救命!!!触发器中,deleted表里有TEXT型的列怎么办?
- 邹建:给几个基础资料表加上triger后。。。。。
- 第一次安装MICROSOFT SQL SERVERS2000桌面引擎(MSDE)成功.卸载后在也安装不成功.老是提示错误.急在线等.指教
- 请问怎么样用一条sql语句删除一张表里的重复纪录?在线等待
- sql中如何把一个数字转化为一个指定位数的串
- 邹建老师:什么时候出MSSQL2005的书呀
insert into tbs select 1,'aaaa',0
insert into tbs select 2,'bbbb',1
insert into tbs select 3,'cccc',2
insert into tbs select 4,'dddd',3create function wsp_p(@ID int)
returns varchar(500)
as
begin
declare @t table(id int,nams varchar(10),fatherid int,Level int)
declare @i int
set @i=1
insert into @t select id,nams,fatherid,@i from tbs where id=@id
while @@rowcount<>0
begin
set @i=@i+1
insert into @t select a.id,a.nams,a.fatherid,@i from tbs a,@t b
where a.id=b.fatherid and b.Level=@i-1
end
declare @sql varchar(1000)
select @sql=isnull(@sql+',','')+nams from @t order by Level desc
return @sql
end
---
select dbo.wsp_p(3)
--
select dbo.wsp_p(4)
创建表
*/
if exists(select * from sysobjects where name ='tbs')
drop table tbs
create table tbs(id int,nams varchar(10),fatherid int)
insert into tbs select 1,'aaaa',0
insert into tbs select 2,'bbbb',1
insert into tbs select 3,'cccc',2
insert into tbs select 4,'dddd',3
go
/*
创建函数
*/
if exists(select 1 from sysobjects where name='f1')
drop function f1
go
create function f1(@id int)
returns varchar(1000)
as
begin
declare @res varchar(1000),@curID int
select @curID=3
while exists(select 1 from tbs where id=@curID)
begin
select @res=nams+','+IsNull(@res,''),@curID=fatherid
from tbs
where id=@curID
end
return left(@res,len(@res)-1)
end
go
--使用函数
select dbo.f1(2)
--result
aaaa,bbbb,cccc
http://blog.csdn.net/roy_88/archive/2008/01/15/2045842.aspx
不超过32层
http://blog.csdn.net/roy_88/archive/2006/12/24/1458449.aspx
go
create table tbs(id int,nams varchar(10),fatherid int)
insert into tbs select 1,'aaaa',0
insert into tbs select 2,'bbbb',1
insert into tbs select 3,'cccc',2
insert into tbs select 4,'dddd',3
gocreate function F_tree(@ID int)
returns nvarchar(100)
as
begin
declare @name nvarchar(100)
select @ID=fatherid,@name=nams from tbs where ID=@ID
if @name is null
return null
return isnull(dbo.F_tree(@ID)+',','')+@Name
endgo
select dbo.F_tree(ID) from tbs
----------------------------------------------------------------------------------------------------
aaaa
aaaa,bbbb
aaaa,bbbb,cccc
aaaa,bbbb,cccc,dddd(所影响的行数为 4 行)