create table if not exists testTable( id INT PRIMARY KEY,[name] NVARCHAR(20), parentId INT );
INSERT INTO testTable(id,[name],parentId) VALUES(1,'xf1',0);
INSERT INTO testTable(id,[name],parentId) VALUES(2,'xf2',0);
INSERT INTO testTable(id,[name],parentId) VALUES(3,'xf3',2);
INSERT INTO testTable(id,[name],parentId) VALUES(4,'xf4',3);
INSERT INTO testTable(id,[name],parentId) VALUES(5,'xf5',4);
INSERT INTO testTable(id,[name],parentId) VALUES(6,'xf6',5);
WITH RECURSIVE
cte(id,name,parentId) AS(
SELECT id,name,parentId FROM testTable WHERE id='6'
UNION ALL
SELECT a.id,a.name,a.parentId FROM testTable AS a INNER JOIN
cte ON a.id=cte.parentId
)
SELECT * FROM cte;
--得到1-10 的数
WITH RECURSIVE
cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<10)
SELECT x FROM cnt order by x;
WITH RECURSIVE
cnt(x) AS (
SELECT 1
UNION ALL
SELECT x+1 FROM cnt
LIMIT 10
)
SELECT x FROM cnt order by x;
参考: 点击打开链接