数据库CTE递归查询

1.已知表结构

pft_Agent(agentCode,agentName,upAgentCode)

2.查询某个节点的所有上级节点

2.1 查询语句

WITH cte
AS (
	SELECT agentCode,agentName,upAgentCode FROM dbo.pft_Agent
	WHERE agentCode='000003'
	UNION ALL
    SELECT  up.agentCode,up.agentName,up.upAgentCode FROM cte 
	INNER JOIN dbo.pft_Agent up ON up.agentCode = cte.upAgentCode
)
select * FROM cte

2.2 查询结果

 

3.查询某个节点的所有下级节点

3.1查询语句

WITH cte
AS (
	SELECT agentCode,agentName,upAgentCode FROM dbo.pft_Agent
	WHERE agentCode='000001'
	UNION ALL
    SELECT  down.agentCode,down.agentName,down.upAgentCode FROM cte 
	INNER JOIN dbo.pft_Agent down ON down.upAgentCode = cte.agentCode
)
select * FROM cte

3.2查询结果

 

4.将查询封装为一个函数

4.1查询所有上级，含自己

CREATE FUNCTION [dbo].[getUpAgent]
(@agentCode NVARCHAR(50))
RETURNS @returnTable TABLE 
( agentCode NVARCHAR(50))
AS
BEGIN
    WITH cte AS (
                SELECT agentCode,agentName,upAgentCode FROM dbo.pft_Agent
                WHERE agentCode=@agentCode
                UNION ALL
                SELECT  up.agentCode,up.agentName,up.upAgentCode FROM cte
                INNER JOIN dbo.pft_Agent up ON up.agentCode = cte.upAgentCode
                    )
                    
    INSERT INTO @returnTable
    select agentCode FROM cte 
    
    RETURN 
END

 4.2查询所有下级，含自己

ALTER FUNCTION [dbo].[getDownAgent]
(@agentCode NVARCHAR(50))
RETURNS @returnTable TABLE 
( agentCode NVARCHAR(50))
AS
BEGIN
    WITH cte AS (
                        SELECT agentCode FROM dbo.pft_Agent
                        WHERE agentCode=@agentCode
                        UNION ALL
                        SELECT  down.agentCode  FROM cte
                        INNER JOIN dbo.pft_Agent down ON down.upAgentCode = cte.agentCode
                    )
                    
    INSERT INTO @returnTable
    select * FROM cte 
    
    RETURN 
END