--有表test0408如下
CREATE TABLE test0408(id int,name varchar(10),pid int)
GOINSERT INTO test0408
SELECT 1,'aa',2 UNION ALL
SELECT 2,'bb',1 UNION ALL
SELECT 3,'cc',1GO/*想要查到如下数据
1,'aa',1
1,'aa',2
2,'bb',2
2,'bb',1
3,'cc',3
3,'cc',1
3,'cc',2
*/--用以下查询出现死循环
WITH X AS
(
SELECT *,ID AS IID FROM test0408 UNION ALL
SELECT test0408.*,X.IID FROM test0408 JOIN X ON test0408.ID=X.PID ----这里是不是添加些什么条件防止死循环
)
SELECT * FROM X ORDER BY IID--求达人指点
CREATE TABLE test0408(id int,name varchar(10),pid int)
GOINSERT INTO test0408
SELECT 1,'aa',2 UNION ALL
SELECT 2,'bb',1 UNION ALL
SELECT 3,'cc',1GO/*想要查到如下数据
1,'aa',1
1,'aa',2
2,'bb',2
2,'bb',1
3,'cc',3
3,'cc',1
3,'cc',2
*/--用以下查询出现死循环
WITH X AS
(
SELECT *,ID AS IID FROM test0408 UNION ALL
SELECT test0408.*,X.IID FROM test0408 JOIN X ON test0408.ID=X.PID ----这里是不是添加些什么条件防止死循环
)
SELECT * FROM X ORDER BY IID--求达人指点
分为递推跟迭代,有入口跟出口,
X出现死循环,因为它没有出口
CREATE TABLE test(id int,name varchar(10),pid int)
GOINSERT INTO test
SELECT 1,'aa',2 UNION ALL
SELECT 2,'bb',1 UNION ALL
SELECT 3,'cc',1
GO;with x as(
select id,name,id iid,pid from test
union all
select t.id,t.name,t.pid,x.pid from test t,x where t.pid=x.id and x.pid<>t.id
)
select id,name,iid from x
union all
select x.id,x.name,t.id from x,test t where x.pid=t.id and x.iid=t.pid order by id
/*
1 aa 1
1 aa 2
2 bb 1
2 bb 2
3 cc 1
3 cc 2
3 cc 3
*/