SQLServer 递归查询 根据子表查询所有父表

根据子表查询所有父表

WFE_FlowRecord表中 WFE_FRCode为主键，WFE_PFRCode为父表的主键

现在想查询某个子表的所有父表的数据，且父表的标志位为WFE_FRSubFlow = 'F' 

使用递归查询查询语句

with cte AS
(    
select WFE_PFRCode,WFE_FRSubFlow,WFE_FRCode,0 as lvl from WFE_FlowRecord -- 子表
where WFE_FRCode ='1000040533'
union ALL
select d.WFE_PFRCode,d.WFE_FRSubFlow,d.WFE_FRCode,lvl+1 
from cte c -- 子表结果集
inner join WFE_FlowRecord d -- 父表
on c.WFE_PFRCode  = d.WFE_FRCode
)
select * from cte -- 父表结果集
WHERE WFE_FRSubFlow = 'F' 

查询结果

写成函数的形式

USE [qdpubErp2015-09-30]
GO
/****** Object:  UserDefinedFunction [dbo].[getpcode1]    Script Date: 10/22/2015 11:37:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[getpcode](@ChildID VARCHAR(50))
returns varchar(50) 
as
BEGIN
	DECLARE @CETParentID varchar(50); 

	with cte AS
	(    
		select WFE_PFRCode,WFE_FRSubFlow,WFE_FRCode,0 as lvl from WFE_FlowRecord 
		where WFE_FRCode =@ChildID
		union ALL
		select d.WFE_PFRCode,d.WFE_FRSubFlow,d.WFE_FRCode,lvl+1 
		from cte c inner join WFE_FlowRecord d on c.WFE_PFRCode  = d.WFE_FRCode
	)
	select @CETParentID=WFE_FRCode from cte 
	WHERE WFE_FRSubFlow = 'F' ;
	return @CETParentID;
END

-- select dbo.getpcode('1000040533')