MSSQL语句递归二叉树

如果是单表内有父子关系，用CTE查询，可找到很多的例子，如果情况复杂点呢？

 

场景是：订单之间有关联关系，表结构被设计成了二叉树结构，列：OrderID （Data）,CopyBy （Left）,CopyFrom（Right）

如果以指定的任意订单号查询出所有与之关联订单，语句如下：

 

--为演示，传@OrderID获取，直接定义了一个
DECLARE @OrderID INT=511000

;WITH RelationOrderInfo  AS (

SELECT OrderID,CopyFrom,CopyBy,1 AS Lev FROM Orders (NOLOCK)
WHERE OrderId=@OrderID
UNION ALL
SELECT A.OrderID,A.CopyFrom,NULL,2 AS Lev
FROM RelationOrderInfo AS R
JOIN  Orders AS A WITH (NOLOCK) ON A.orderid=R.CopyFrom
UNION ALL
SELECT A.OrderID,NULL,A.CopyBy,3 AS Lev
FROM RelationOrderInfo AS R
JOIN  Orders AS A WITH (NOLOCK) ON A.orderid=R.CopyBy

)
SELECT * FROM RelationOrderInfo

为便于理解，加入了Lev列查看。

关键处是两NULL值的传递，

像不像前序？

 

转载于:https://www.cnblogs.com/stevenmoli/archive/2012/08/24/2654592.html