比如有张表,结构如下:
Example(ID int,ParentID int)
取其某个ID为父节点的树结构的SQL如下:
WITH Example_Table(ID,ParentID)
AS
(
--取根节点放入临时表
SELECT ID,ParentID FROM Example WHERE ID = @ID
--根据已取到的数据递归取其字节点的数据
UNION ALL
SELECT ID,ParentID FROM Example A INNER JOIN Example_Table B ON A.ParentID = B.ID
)
SELECT * FROM Example_Table