[SQL语句实现子孙树查询经典实例]坟边种什么树旺子孙

下面介绍的SQL语句非常经典，该SQL语句实现子孙树查询，该SQL语句可以直接在查询分析器中执行，供您参考。

--生成表 create table MENU(id int,mname char(50),parent int) --插入数据 insert into MENU select 1,"新闻",Null union all select 2,"房产",Null union all select 3,"科技新闻",1 union all select 4,"社会新闻",1 union all select 5, "IT新闻",3 union all select 6, "航天新闻",3 --实现查询新闻子孙树 Declare @s varchar(1000) select @s=","+cast(id as varchar(20))+"" from MENU where id=1 while @@rowCount>0 --charindex:返回字符串中指定表达式的起始位置 select @s=@s+","+cast(id as varchar) from MENU where charindex(","+cast(id as varchar)+",",@s+",")=0 and charindex(","+cast(parent as varchar)+",",@s+",")>0 select * from MENU where charindex(","+cast(id as varchar)+",",@s+",")>0 --删除表 drop table MENU