sql2000 查询 所有递归的子节点
2020-8-26 糖果小宝 sql
Declare @s varchar(1000)
select @s=','+cast(id as varchar(20))+'' from M_Directory where id=5
while @@rowCount>0
--charindex:返回字符串中指定表达式的起始位置
select @s=@s+','+cast(id as varchar) from M_Directory
where charindex(','+cast(id as varchar)+',',@s+',')=0
and charindex(','+cast(MainID as varchar)+',',@s+',')>0
select * from M_Directory where charindex(','+cast(id as varchar)+',',@s+',')>0
sql2008查询所有递归的子节点的语句
WITH COMMENT_CTE(id,MainID,name)
AS
(
--基本语句
SELECT id,MainID,name FROM M_Directory
WHERE id = 1
UNION ALL --递归语句
SELECT c.id,c.MainID,c.name FROM M_Directory AS c
INNER JOIN COMMENT_CTE AS ce --递归查询
ON c.MainID = ce.id
)
SELECT * FROM COMMENT_CTE
发表评论：