递归一般出现在树形结构中
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