表:table
ID PID
1 2
2 3
3 4
4 5查询当ID为2时,期待结果是:
ID PID
2 3
3 4
4 5自己写的有点问题,总是提示 语句被终止。完成执行语句前已用完最大递归 100。with tmp(ID,PID,LEV) as
(select ID,PID,LEV=1 FROM table where ID=2
UNION ALL SELECT table.ID,table.PID,tmp.LEV+1 FROM tmp,table
where tmp.PID=table.ID
)
select * from tmp
ID PID
1 2
2 3
3 4
4 5查询当ID为2时,期待结果是:
ID PID
2 3
3 4
4 5自己写的有点问题,总是提示 语句被终止。完成执行语句前已用完最大递归 100。with tmp(ID,PID,LEV) as
(select ID,PID,LEV=1 FROM table where ID=2
UNION ALL SELECT table.ID,table.PID,tmp.LEV+1 FROM tmp,table
where tmp.PID=table.ID
)
select * from tmp
from table
where PID > 2
不行么?
--> 测试数据:[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([ID] INT,[PID] INT)
INSERT [tb]
SELECT 1,2 UNION ALL
SELECT 2,3 UNION ALL
SELECT 3,4 UNION ALL
SELECT 4,5
--------------开始查询--------------------------
;WITH t AS
(
SELECT a.* FROM [tb] AS a WHERE [PID]=2
UNION ALL
SELECT a.* FROM [tb] AS a,t WHERE t.[PID]=a.[ID]
)
SELECT * FROM t WHERE [PID]!=2
----------------结果----------------------------
/*
ID PID
----------- -----------
2 3
3 4
4 5(3 行受影响)
*/
USE tempdb
GOCREATE TABLE test
(id INT,pid int)INSERT INTO test
SELECT 1,2
UNION ALL
SELECT 2,3
UNION ALL
SELECT 3,4
UNION ALL
SELECT 4,5SELECT * FROM test/*
查询当ID为2时,期待结果是:
ID PID
2 3
3 4
4 5
*/
WITH cte (id,pid)
AS
(
SELECT id,pid
FROM test
WHERE id=2
UNION ALL
SELECT b.id,b.pid
FROM cte a INNER JOIN test b ON a.pid=b.id
)
SELECT * FROM cte