SQL递归

递归一般出现在树形结构中

1：根据孩子节点查找所有父节点

With T
As
(
Select * From U_Companies TB Where CompanyID=80047
Union All
Select TB.* From U_Companies TB Inner Join T on TB.CompanyID=T.ParentID
)
Select * From T

　　

 

2：根据父亲查找所有子节点

 

With T
As
(
Select * From U_Companies TB Where CompanyID=80047
Union All
Select TB.* From U_Companies TB Inner Join T on TB.ParentID=T.CompanyID
)
Select * From T

 

如果CompanyID多个

declare @CompanyRange varchar(1000)=''
set @CompanyRange='80084,80085'

;With T
	As
	(
		SELECT TB.* FROM 
		(
			SELECT T1.* From 
				U_Companies T1 
				INNER JOIN 
				(SELECT F1 AS CompanyID  FROM dbo.SplitStrToTable(@CompanyRange,',')) T2
			ON T1.CompanyID=T2.CompanyID
		) TB
	Union All
	Select TB.*
	From U_Companies TB Inner Join T on TB.ParentID=T.CompanyID
	)
	Select * From T ORDER BY CompanyID;

　　

SplitStrToTable函数代码：

ALTER   function   [dbo].[SplitStrToTable]
(
    @SourceSql   VARCHAR(8000),
    @StrSeprate   VARCHAR(100))
    RETURNS   @temp   TABLE(F1   VARCHAR(100)
)   
AS     
BEGIN   
  DECLARE   @ch  AS   VARCHAR(100)   
  SET   @SourceSql=@SourceSql+@StrSeprate     
  WHILE(@SourceSql<>'')   
  BEGIN   
		SET   @ch=left(@SourceSql,CHARINDEX(',',@SourceSql,1)-1)   
		INSERT   @temp   VALUES(@ch)   
		SET   @SourceSql=stuff(@SourceSql,1,charindex(',',@SourceSql,1),'')   
  END   
  RETURN   
END