文章/答案/技术大牛

发布

社区首页 >问答首页 >CTE代码只是抓住锚而不进行递归

问CTE代码只是抓住锚而不进行递归
EN

Stack Overflow用户

提问于 2017-09-14 09:25:07

回答 1查看 34关注 0票数 0

查询不是递归，它只获取锚部分(父id为null的id)。

有人能帮我找出我的错误吗？我尝试了很多东西，但我总是检索相同数量的行，即没有父I的ids。

WITH EmpTree
AS (
    SELECT e.DWH_Dim_TFS_File_DWH_FileParent_Guid
        ,e.DWH_Dim_TFS_File_DWH_File_Guid
        ,1 AS Depth
        ,File_Name_String = CAST(CAST(e.File_Name AS BINARY (100)) AS VARBINARY(8000))
    FROM [dbo].[Hierarchy_Luis] AS e
    WHERE e.DWH_Dim_TFS_File_DWH_FileParent_Guid IS NULL

    UNION ALL

    SELECT e.DWH_Dim_TFS_File_DWH_FileParent_Guid
        ,e.DWH_Dim_TFS_File_DWH_File_Guid
        ,p.Depth + 1 AS Depth
        ,File_Name_String = CAST(CONCAT (
                p.File_Name_String
                ,CAST(e.File_Name AS BINARY (100))
                ) AS VARBINARY(8000))
    FROM [dbo].[Hierarchy_Luis] AS e
    JOIN EmpTree AS p ON e.DWH_Dim_TFS_File_DWH_FileParent_Guid = p.DWH_Dim_TFS_File_DWH_File_Guid
    )
SELECT p.DWH_Dim_TFS_File_DWH_File_Guid
    ,p.Depth
    ,Level01 = CAST(SUBSTRING(p.File_Name_String, 1, 100) AS NVARCHAR(100))
    ,Level02 = CAST(SUBSTRING(p.File_Name_String, 101, 100) AS NVARCHAR(100))
    ,Level03 = CAST(SUBSTRING(p.File_Name_String, 201, 100) AS NVARCHAR(100))
    ,Level04 = CAST(SUBSTRING(p.File_Name_String, 301, 100) AS NVARCHAR(100))
    ,Level05 = CAST(SUBSTRING(p.File_Name_String, 401, 100) AS NVARCHAR(100))
    ,Level07 = CAST(SUBSTRING(p.File_Name_String, 501, 100) AS NVARCHAR(100))
    ,Level08 = CAST(SUBSTRING(p.File_Name_String, 601, 100) AS NVARCHAR(100))
    ,Level09 = CAST(SUBSTRING(p.File_Name_String, 701, 100) AS NVARCHAR(100))
    ,Level10 = CAST(SUBSTRING(p.File_Name_String, 801, 100) AS NVARCHAR(100))
    ,Level11 = CAST(SUBSTRING(p.File_Name_String, 901, 100) AS NVARCHAR(100))
    ,Level12 = CAST(SUBSTRING(p.File_Name_String, 1001, 100) AS NVARCHAR(100))
    ,Level13 = CAST(SUBSTRING(p.File_Name_String, 1101, 100) AS NVARCHAR(100))
    ,Level14 = CAST(SUBSTRING(p.File_Name_String, 1201, 100) AS NVARCHAR(100))
    ,Level15 = CAST(SUBSTRING(p.File_Name_String, 1301, 100) AS NVARCHAR(100))
    ,Level16 = CAST(SUBSTRING(p.File_Name_String, 1401, 100) AS NVARCHAR(100))
    ,Level17 = CAST(SUBSTRING(p.File_Name_String, 1501, 100) AS NVARCHAR(100))
    ,Level18 = CAST(SUBSTRING(p.File_Name_String, 1601, 100) AS NVARCHAR(100))
    ,Level19 = CAST(SUBSTRING(p.File_Name_String, 1701, 100) AS NVARCHAR(100))
    ,Level20 = CAST(SUBSTRING(p.File_Name_String, 1801, 100) AS NVARCHAR(100))
    ,Level21 = CAST(SUBSTRING(p.File_Name_String, 1901, 100) AS NVARCHAR(100))
    ,Level22 = CAST(SUBSTRING(p.File_Name_String, 2001, 100) AS NVARCHAR(100))
    ,Level23 = CAST(SUBSTRING(p.File_Name_String, 2101, 100) AS NVARCHAR(100))
    ,Level24 = CAST(SUBSTRING(p.File_Name_String, 2201, 100) AS NVARCHAR(100))
    ,Level25 = CAST(SUBSTRING(p.File_Name_String, 2301, 100) AS NVARCHAR(100))
    ,Level26 = CAST(SUBSTRING(p.File_Name_String, 2401, 100) AS NVARCHAR(100))
    ,Level27 = CAST(SUBSTRING(p.File_Name_String, 2501, 100) AS NVARCHAR(100))
FROM EmpTree p

您可以在链接中看到正在进行递归的两列数据类型，即nvarchar(256)。

非常感谢，我知道这一定是很基本的事情，但是我找不到。

样本数据

DWH_Dim_TFS_File_DWH_FileParent_GUID    DWH_Dim_TFS_File_DWH_File_GUID  File_Name
114087616   114087617   ot_de
114087616   114087618   ot_de
114087593   114087619   _proj
11407441    11408762    I_EXP
114087593   114087620   arm\
114087620   114087621   e\
114087621   114087622   ot_de
114087621   114087623   ot_de
114087593   114087624   c\
114087624   114087625   ot_de
114087624   114087626   ot_de
114087593   114087627   3s896
114087593   114087628   kefil
114087593   114087629   adme.
11408762    11408763    B_PTS
114087593   114087630   mdk\
114087630   114087631   ot_de
114087630   114087632   ot_de
114087593   114087633   urcer
114087633   114087634   ot_de
114087633   114087635   ot_de
114087593   114087636   artup
114087593   114087637   artup
114087593   114087638   artup
114087593   114087639   artup
11408763    11408764    B_PTS
114087593   114087640   artup
114087302   114087641   ot_et
114087641   114087642   _conf
114087641   114087643   ot_et
114087641   114087644   ot_et
114087641   114087645   ot_et
114087641   114087646   ot_et
114087641   114087647   ot_et
114087641   114087648   s\
114087648   114087649   csimp
11408763    11408765    B_PTS
114087648   114087650   cspro
114087648   114087651   dtbui
114087648   114087652   dtpro
114087648   114087653   rojec
114087648   114087654   ettin
114087654   114087655   g.ecl
114087648   114087656   cros.
114087648   114087657   rget_
114087641   114087658   dered
114087658   114087659   ot_et
11408763    11408766    cessW

sql-server

tsql

recursion

ssms

common-table-expression

回答 1

Stack Overflow用户

回答已采纳

发布于 2017-09-14 10:21:33

这是基于前面的评论的答案：

解决方案是，他没有任何匹配的地方，他们是空的-他必须匹配他们在第一选择。

WITH EmpTree
AS (
SELECT e.DWH_Dim_TFS_File_DWH_FileParent_Guid
    ,e.DWH_Dim_TFS_File_DWH_File_Guid
    ,1 AS Depth
    ,File_Name_String = CAST(CAST(e.File_Name AS BINARY (100)) AS VARBINARY(8000))

FROM [dbo].[Hierarchy_Luis] AS e WHERE e.DWH_Dim_TFS_File_DWH_FileParent_Guid = e.DWH_Dim_TFS_File_DWH_File_Guid

UNION ALL

SELECT e.DWH_Dim_TFS_File_DWH_FileParent_Guid
    ,e.DWH_Dim_TFS_File_DWH_File_Guid
    ,p.Depth + 1 AS Depth
    ,File_Name_String = CAST(CONCAT (
            p.File_Name_String
            ,CAST(e.File_Name AS BINARY (100))
            ) AS VARBINARY(8000))
FROM [dbo].[Hierarchy_Luis] AS e
JOIN EmpTree AS p ON e.DWH_Dim_TFS_File_DWH_FileParent_Guid = p.DWH_Dim_TFS_File_DWH_File_Guid
)
SELECT p.DWH_Dim_TFS_File_DWH_File_Guid
,p.Depth
,Level01 = CAST(SUBSTRING(p.File_Name_String, 1, 100) AS NVARCHAR(100))
,Level02 = CAST(SUBSTRING(p.File_Name_String, 101, 100) AS NVARCHAR(100))
,Level03 = CAST(SUBSTRING(p.File_Name_String, 201, 100) AS NVARCHAR(100))
,Level04 = CAST(SUBSTRING(p.File_Name_String, 301, 100) AS NVARCHAR(100))
,Level05 = CAST(SUBSTRING(p.File_Name_String, 401, 100) AS NVARCHAR(100))
,Level07 = CAST(SUBSTRING(p.File_Name_String, 501, 100) AS NVARCHAR(100))
,Level08 = CAST(SUBSTRING(p.File_Name_String, 601, 100) AS NVARCHAR(100))
,Level09 = CAST(SUBSTRING(p.File_Name_String, 701, 100) AS NVARCHAR(100))
,Level10 = CAST(SUBSTRING(p.File_Name_String, 801, 100) AS NVARCHAR(100))
,Level11 = CAST(SUBSTRING(p.File_Name_String, 901, 100) AS NVARCHAR(100))
,Level12 = CAST(SUBSTRING(p.File_Name_String, 1001, 100) AS NVARCHAR(100))
,Level13 = CAST(SUBSTRING(p.File_Name_String, 1101, 100) AS NVARCHAR(100))
,Level14 = CAST(SUBSTRING(p.File_Name_String, 1201, 100) AS NVARCHAR(100))
,Level15 = CAST(SUBSTRING(p.File_Name_String, 1301, 100) AS NVARCHAR(100))
,Level16 = CAST(SUBSTRING(p.File_Name_String, 1401, 100) AS NVARCHAR(100))
,Level17 = CAST(SUBSTRING(p.File_Name_String, 1501, 100) AS NVARCHAR(100))
,Level18 = CAST(SUBSTRING(p.File_Name_String, 1601, 100) AS NVARCHAR(100))
,Level19 = CAST(SUBSTRING(p.File_Name_String, 1701, 100) AS NVARCHAR(100))
,Level20 = CAST(SUBSTRING(p.File_Name_String, 1801, 100) AS NVARCHAR(100))
,Level21 = CAST(SUBSTRING(p.File_Name_String, 1901, 100) AS NVARCHAR(100))
,Level22 = CAST(SUBSTRING(p.File_Name_String, 2001, 100) AS NVARCHAR(100))
,Level23 = CAST(SUBSTRING(p.File_Name_String, 2101, 100) AS NVARCHAR(100))
,Level24 = CAST(SUBSTRING(p.File_Name_String, 2201, 100) AS NVARCHAR(100))
,Level25 = CAST(SUBSTRING(p.File_Name_String, 2301, 100) AS NVARCHAR(100))
,Level26 = CAST(SUBSTRING(p.File_Name_String, 2401, 100) AS NVARCHAR(100))
,Level27 = CAST(SUBSTRING(p.File_Name_String, 2501, 100) AS NVARCHAR(100))
FROM EmpTree p

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/46215309

复制

相似问题

问CTE代码只是抓住锚而不进行递归
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问CTE代码只是抓住锚而不进行递归EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问CTE代码只是抓住锚而不进行递归
EN