SqlServer的With递归查询子父级

工作中有一个需求，要判断客户是否有后续订单，就是查后面的订单是否此客户ID下单， 而且要把此客户的所有关联的客户也都判断上

这有点头痛，因为关联客户是一个嵌套型父子级的结构，客户A关联客户B，客户B关联客户C，客户C关联客户D，无论取客户A、B、C、D任一一个去查，都要把整个关联关系的客户A、B、C、D全都查出来进行判断

并且要在select字段中使用，还要能批量使用，研究了关天发现可以用表值函数实现

ALTER FUNCTION [dbo].[GetCustomerConnectionRelations]
(    
    -- Add the parameters for the function here
    @CustomerId UNIQUEIDENTIFIER
)
-- Add the SELECT statement with parameter references here
    
RETURNS TABLE 
AS
RETURN 
(
    WITH cte AS (
        SELECT cc1.SourceCustomerId ,cc1.ConnectionCustomerId FROM co_crm.dbo.CustomerConnections AS cc1 WHERE cc1.SourceCustomerId=@CustomerId
        UNION ALL 
        SELECT cc2.SourceCustomerId,cc2.ConnectionCustomerId FROM co_crm.dbo.CustomerConnections AS cc2 INNER JOIN cte AS c ON cc2.ConnectionCustomerId= c.SourceCustomerId
    ),
    cte2 AS (
        SELECT cc1.SourceCustomerId ,cc1.ConnectionCustomerId FROM co_crm.dbo.CustomerConnections AS cc1 WHERE cc1.SourceCustomerId=@CustomerId
        UNION ALL 
        SELECT cc2.SourceCustomerId,cc2.ConnectionCustomerId FROM co_crm.dbo.CustomerConnections AS cc2 INNER JOIN cte2 AS c ON cc2.SourceCustomerId= c.ConnectionCustomerId
    )    
    SELECT @CustomerId AS CustomerId
    UNION 
    SELECT ConnectionCustomerId FROM cte
    UNION 
    SELECT ConnectionCustomerId FROM cte2
)

 

这样就可以在Sql里面的Select字段中使用了

SELECT CAST( ISNULL((SELECT TOP 1 1 FROM Orders o WHERE o.CustomerId IN (SELECT * FROM co_crm.dbo.GetCustomerConnectionRelations(t1.CustomerId))),0) AS BIT) AS '是否有后续订单' FROM  Table1 t1