Understanding with recursive calls to SQL Server tree queries

-- With usage -- so you can understand .  with  as (    SQL statement)Select* from SQL statement variable or called temporary table name

--Recursive invocation withCte as(    SelectZtbm_id,ztbm_name,parentid fromTb_zym_ztbmwhereParentID is NULL orParentID="'    Union  All    SelectA.ztbm_id,a.ztbm_name,a.parentid fromTB_ZYM_ZTBM AInner Join CTE  onA.parentid=CTE. ZTBM_ID)Select *  fromCTE--This statement can be understood as follows

--same Previous statement    --Top Level 1    SelectZtbm_id,ztbm_name,parentid fromTb_zym_ztbmwhereParentID is NULL orParentID="'    Union  All    SelectA.ztbm_id,a.ztbm_name,a.parentid fromTB_ZYM_ZTBM aInner Join--Here the brackets can be understood as the topmost CTE where the red CTE is the CTE of the previous SQL statement (--Second Floor        SelectZtbm_id,ztbm_name,parentid fromTb_zym_ztbmwhereParentID is NULL orParentID="'        Union  All        SelectA.ztbm_id,a.ztbm_name,a.parentid fromTB_ZYM_ZTBM aInner Join--The parentheses here can be understood as the second-level CTE (--Third Floor            SelectZtbm_id,ztbm_name,parentid fromTb_zym_ztbmwhereParentID is NULL orParentID="'            Union  All            SelectA.ztbm_id,a.ztbm_name,a.parentid fromTB_ZYM_ZTBM aInner Join             (                --Fourth Floor                SelectZtbm_id,ztbm_name,parentid fromTb_zym_ztbmwhereParentID is NULL orParentID="'                Union  All                SelectA.ztbm_id,a.ztbm_name,a.parentid fromTB_ZYM_ZTBM aInner Join                 (                    SelectZtbm_id,ztbm_name,parentid fromTb_zym_ztbmwhereParentID is NULL orParentID="'                    --INNER join .... Layer Nesting                    --the bottom) CTE onA.parentid=CTE. ztbm_id) CTE onA.parentid=CTE. ztbm_id) CTE onA.parentid=CTE. ztbm_id) CTE onA.parentid=Cte. ztbm_id

Variations of the following query (sort, and split)

 withCte as    (     -->begin A locator point member Selectztbm_id, Ztbm_name,parentid,cast(Ztbm_name as nvarchar(Max)) asTE, row_number () Over(Order  by getdate()) asOrderID,0  asLevle fromTb_zym_ztbmwhereDeletemark=1  and(ParentID is NULL orParentID="') -->endUnion  All     -->begin a recursive member SelectTB_ZYM_ZTBM. ztbm_id, TB_ZYM_ZTBM.ZTBM_NAME,TB_ZYM_ZTBM. ParentID,cast(Replicate(' 　　 ', Levle+1)+'|_'+Tb_zym_ztbm.ztbm_name as nvarchar(MAX)) asTE, CTE. OrderID* -+Row_number () Over(Order  by GETDATE()) asOrderID, Levle+1  asLevle fromTb_zym_ztbmInner JoinCTE onTB_ZYM_ZTBM. ParentID=CTE. ztbm_id-->end)     Select *  fromCTEOrder  by LTRIM(OrderID)

Reference: http://blog.csdn.net/bin_520_yan/article/details/5998349

http://msdn.microsoft.com/zh-cn/library/ms175972 (sql.105). aspx

Understanding with recursive calls to SQL Server tree queries