表结构如下:
id int 主键
name varchar(50)
parentid int
id name parentid
1 a1aa 0
2 bbb 1
3 af 2
4 aff 2
5 aaa 1
6 asa 4
这个分类是无限极,请给出sql 语句,谢谢
------解决方案--------------------
- SQL code
-->Title:Generating test data-->Author:wufeng4552-->Date :2009-09-30 08:52:38set nocount onif object_id('tb','U')is not null drop table tbgocreate 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_GetChildIDgocreate function Uf_GetChildID(@ParentID int)returns @t table(ID int)asbegin insert @t select ID from tb where [email protected] 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 returnendgoselect * from dbo.Uf_GetChildID(5)/*ID-----------67*/-->Title:查找指定節點的所有父結點if object_id('Uf_GetParentID')is not null drop function Uf_GetParentIDgocreate function Uf_GetParentID(@ID int)returns @t table(ParentID int)asbegin insert @t select ParentID from tb where [email protected] 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 returnendgoselect * from dbo.Uf_GetParentID(2)/*ParentID-----------10*/本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/wufeng4552/archive/2009/09/30/4619995.aspx
------解决方案--------------------
- SQL code
;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
------解决方案--------------------
- SQL code
if object_id('[tb]') is not null drop table [tb]gocreate table [tb]([id] int,[name] varchar(4),[parentid] int)insert [tb]select 1,'a1aa',0 union allselect 2,'bbb',1 union allselect 3,'af',2 union allselect 4,'aff',2 union allselect 5,'aaa',1 union allselect 6,'asa',4go;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 02 bbb 14 aff 26 asa 4(4 行受影响)**/
------解决方案--------------------
- SQL code
--> 测试数据：#testif object_id('tempdb.dbo.#test') is not null drop table #testcreate table #test([id] int,[name] varchar(4),[parentid] int)insert #testselect 1,'a1aa',0 union allselect 2,'bbb',1 union allselect 3,'af',2 union allselect 4,'aff',2 union allselect 5,'aaa',1 union allselect 6,'asa',4declare @id intset @id=6;with tas(select * from #test where [email protected]union allselect a.* from #test ainner join t bon a.id=b.parentid)select parentid from t/*parentid4210*/