递归CTE SQL (父子)

Question

我使用的是SQL Server 2012

LOCATIONDETAIL表

OID     LOCATIONNAME    PARENTID
1           GLOBAL          0
2           NORTH           1
3           SOUTH           1
4           NORTH1          2
5           SOUTH1          3

LOCATIONSITECONFIG表

OID LOCATIONID  SITENAME
1       2        TEST

我使用递归CTE查询

;WITH LOCALSITEHIERARCHY AS
(
    SELECT  A.OID
            ,A.PARENTOID
            ,CAST(A.LOCATIONNAME + ' ( ' + LSC.SITENAME + ' )' AS NVARCHAR(100)) AS NAME
            ,LSC.OID AS SITEOID
    FROM LOCATIONDETAIL A
            INNER JOIN LOCATIONSITECONFIG LSC 
                ON LSC.LOCATIONDETAILOID = A.OID                
    WHERE
            LSC.SITENAME <> 'GLOBAL' AND LSC.RECSTATUS = 'A'
    UNION ALL
    SELECT 
        A.OID
        ,A.PARENTOID
        ,CAST(A.LOCATIONNAME AS NVARCHAR(100))                  
        ,LH.SITEOID 
    FROM LOCATIONDETAIL A 
        INNER JOIN LOCALSITEHIERARCHY LH ON A.PARENTOID = LH.OID
)

SELECT * FROM LOCALSITEHIERARCHY

北向现在是一个单独的站点在Global.This查询返回北向和北向1在树结构中，这是正常的。

当我将Global创建为一个新站点时，我不想将North作为Global site的一部分。

例如，添加名为(NEWTEST)的全局站点2

LOCATIONSITECONFIG表

OID LOCATIONDETAILOID   SITENAME
1       2               TEST
2       1               NEWTEST

上面的查询返回全局、北、测试、南、SOUTH1，然后返回北(测试)和NORTH1 (重复记录)

我希望查询返回全局、北(测试)、NORTH1、南、SOUTH1

如果子站点已创建，则查询应忽略该站点。请帮帮忙

Answer 1

将您的CTE更改为以全局作为锚点成员。递归部分需要调整以包括SITENAME，这是由一个联合(如果一个位置没有站点名称)和子查询来完成的。

with LOCALSITEHIERARCHY
     as (
     select A.OID
          , A.PARENTOID
          , cast(A.LOCATIONNAME+' ( '+LSC.SITENAME+' )' as nvarchar(100)) as NAME
          , LSC.OID as SITEOID
          , cast(row_number() over(partition by parentoid order by A.LOCATIONNAME) as varchar(max)) as [PATH]
     from LOCATIONDETAIL as A
          inner join LOCATIONSITECONFIG as LSC on LSC.LOCATIONDETAILOID = A.OID
     where 1 = 1
           and A.PARENTOID is null
           and LSC.RECSTATUS = 'A'
     union all
     select A.OID
          , A.PARENTOID
          , cast(coalesce(A.LOCATIONNAME+' ( '+
                         (
                             select SITENAME
                             from LOCATIONSITECONFIG C
                             where C.LOCATIONDETAILOID = A.OID
                         )+' )', A.LOCATIONNAME) as nvarchar(100)) as NAME
          , coalesce((select C.OID from LOCATIONSITECONFIG C where C.LOCATIONDETAILOID = A.OID),NULL) as SITEOID
          , [path]+'-'+cast(row_number() over(partition by A.parentoid order by A.LOCATIONNAME) as varchar(max))
     from LOCATIONDETAIL as A
          inner join LOCALSITEHIERARCHY as LH on A.PARENTOID = LH.OID)

     select * from LOCALSITEHIERARCHY order by [PATH];

结果输出如下图所示。

​

​