(int,int,varchar(10),int DECLARE @id AS INT SET @id =1; WITH ACTE(id,parentid,name,level) AS ( SELECT id,parentid,name,level,0 FROM A WHERE parentid=@id UNION ALL SELECT ta.id,ta.parentid,ta.name,TB.level+1 FROM A TA INNER JOIN ACTE TB ON TA.PARENTID=TB.ID ) SELECT * FROM ACTE
DECLARE @id AS INT
SET @id =1;
WITH ACTE(id,parentid,name,level)
AS
(
SELECT id,parentid,name,level,0
FROM A WHERE parentid=@id
UNION ALL
SELECT ta.id,ta.parentid,ta.name,TB.level+1
FROM A TA INNER JOIN ACTE TB
ON TA.PARENTID=TB.ID
)
SELECT * FROM ACTE