-- 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