表结构如下:id int 主键name varchar(50)parentid int id name parentid 1 a1aa 02 bbb 1
3 af 2
4 aff 2
5 aaa 1
6 asa 4这个分类是无限极,请给出sql 语句,谢谢
3 af 2
4 aff 2
5 aaa 1
6 asa 4这个分类是无限极,请给出sql 语句,谢谢
解决方案 »
- 求解·····无法绑定有多个部分组成的标识符
- 本人菜鸟 求救菜鸟问题 关于sql语句!!
- 急!!!只有一个LDF文件,MDF文件被删除了,能恢复吗?求方法!(100分赠送)
- 匹配问题,谢谢各位帮忙,很急!
- SQL2000的数据库的日志文件被删除了,现在只有一个MDF文件,请大家帮帮我,要怎么才能恢复数据库?救命!!
- 100分相送
- 特别感谢:weixxxp(想一想),bluepower2008(蓝色力量),Haiwer(海阔天空)等先生的帮助。我的程序基本完成了。就是谈价钱方面,小弟还请各位帮个忙。
- 三层结构,后台Access,如何实现类似SQL Server的触发器功能?绝对高分
- 将tb1的中的记录插入到tb2时报此错,该咋解决?
- 无法创建链接服务器的前提下,如何访问另外一台访问的数据
- 如何在每年的最后一天和每个2月的最后一天执行一个存储过程
- 请教:如何循环拆分字符串?
-->Title:Generating test data
-->Author:wufeng4552
-->Date :2009-09-30 08:52:38
set nocount on
if object_id('tb','U')is not null drop table tb
go
create table tb(ID int, ParentID int)
insert into tb select 1,0
insert into tb select 2,1
insert into tb select 3,1
insert into tb select 4,2
insert into tb select 5,3
insert into tb select 6,5
insert into tb select 7,6
-->Title:查找指定節點下的子結點
if object_id('Uf_GetChildID')is not null drop function Uf_GetChildID
go
create function Uf_GetChildID(@ParentID int)
returns @t table(ID int)
as
begin
insert @t select ID from tb where ParentID=@ParentID
while @@rowcount<>0
begin
insert @t select a.ID from tb a inner join @t b
on a.ParentID=b.id and
not exists(select 1 from @t where id=a.id)
end
return
end
go
select * from dbo.Uf_GetChildID(5)
/*
ID
-----------
6
7
*/
-->Title:查找指定節點的所有父結點
if object_id('Uf_GetParentID')is not null drop function Uf_GetParentID
go
create function Uf_GetParentID(@ID int)
returns @t table(ParentID int)
as
begin
insert @t select ParentID from tb where ID=@ID
while @@rowcount!=0
begin
insert @t select a.ParentID from tb a inner join @t b
on a.id=b.ParentID and
not exists(select 1 from @t where ParentID=a.ParentID)
end
return
end
go
select * from dbo.Uf_GetParentID(2)
/*
ParentID
-----------
1
0
*/本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/wufeng4552/archive/2009/09/30/4619995.aspx
(select * from tb where name='asa'
union all
select a.* from tb a,cte b where a.id=b.parentid
)
select * from cte order by id
go
create table [tb]([id] int,[name] varchar(4),[parentid] int)
insert [tb]
select 1,'a1aa',0 union all
select 2,'bbb',1 union all
select 3,'af',2 union all
select 4,'aff',2 union all
select 5,'aaa',1 union all
select 6,'asa',4
go;with cte as
(select * from tb where name='asa'
union all
select a.* from tb a,cte b where a.id=b.parentid
)
select * from cte order by id
/**
id name parentid
----------- ---- -----------
1 a1aa 0
2 bbb 1
4 aff 2
6 asa 4(4 行受影响)
**/
--> 测试数据:#test
if object_id('tempdb.dbo.#test') is not null drop table #test
create table #test([id] int,[name] varchar(4),[parentid] int)
insert #test
select 1,'a1aa',0 union all
select 2,'bbb',1 union all
select 3,'af',2 union all
select 4,'aff',2 union all
select 5,'aaa',1 union all
select 6,'asa',4
declare @id int
set @id=6
;with t
as(
select * from #test where ID=@id
union all
select a.* from #test a
inner join t b
on a.id=b.parentid
)
select parentid from t
/*
parentid
4
2
1
0
*/