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值的传递,
像不像前序?