时间戳

首页 > 解决方案 > SQL Recursive CTE 在每次递归中替换记录

sql - SQL Recursive CTE 在每次递归中替换记录

问题描述

我有一张这样的桌子：

ItemID  ItemFormula
100     'ID_3+ID_5'
110     'ID_2+ID_6'
120     'ID_100+ID_110'
130     'ID_120+ID_4'

这是一个公式表的简化版本，包含近 1000 条记录和多达 40 级参考（用于其他项目的项目）。任务是将公式分解为仅一个级别的参考，其中一个项目中没有其他项目。例如在上表中 id=130 我应该有 '((ID_3+ID_5)+(ID_2+ID_6))+ID_4'

编辑：操作不限于“+”并且项目之间有一个可识别的字符。为了简单起见，我删除了那个字符。

我可以为此使用递归 CTE。但我的问题是，由于参考水平很高，我的递归选择有很多记录加入，所以需要很多时间才能完成。

我的问题是：我可以只在每次递归发生时保留以前的递归吗？

这是我的 CTE 代码

WITH Formula
  AS  (SELECT A.ItemID
             ,'ID_' + CONVERT(VARCHAR(20), A.ItemID) AS ItemText
             ,CONVERT(VARCHAR(MAX), A.ItemFormula) AS ItemFormula 
       FROM (VALUES (100,'ID_3+ID_5'),
                    (110,'ID_2+ID_6'),
                    (120,'ID_100+ID_110'),
                    (130,'ID_120+ID_4')   
                ) A (ItemID,ItemFormula)

  )
    ,REC
  AS
      (
          SELECT A.ItemID
                ,A.ItemText
                ,A.ItemFormula
                ,1 AS LevelID
          FROM Formula A
          UNION ALL
          SELECT A.ItemID
                ,A.ItemText
                ,' '
                 + TRIM (REPLACE (REPLACE (A.ItemFormula, B.ItemText, ' ( ' + B.ItemFormula + ' ) '), '  ', ' '))
                 + ' ' AS ItemFormula
                ,A.LevelID + 1 AS LevelID
          FROM REC A
              CROSS APPLY
          (
              SELECT *
              FROM
              (
                  SELECT *
                        ,ROW_NUMBER () OVER (ORDER BY GETDATE ()) AS RowNum
                  FROM Formula B2
                  WHERE CHARINDEX (B2.ItemText, A.ItemFormula) > 0
              ) B3
              WHERE B3.RowNum = 1
          ) B
      )
    ,FinalQ
  AS
      (
          SELECT A2.ItemID
                ,A2.ItemFormula
                ,A2.LevelID
          FROM
          (
              SELECT A.ItemID
                    ,REPLACE (TRIM (A.ItemFormula), ' ', '') AS ItemFormula
                    ,A.LevelID
                    ,ROW_NUMBER () OVER (PARTITION BY A.ItemID ORDER BY A.LevelID DESC) AS RowNum
              FROM REC A
          ) A2
          WHERE A2.RowNum = 1
      )
SELECT * FROM FinalQ A2 ORDER BY A2.ItemID;

提前致谢。

标签： sqlsql-serverrecursionreplace

解决方案

我的问题是：我可以只在每次递归发生时保留以前的递归吗？

不会。递归 CTE 将继续向先前迭代中找到的行添加行。您没有某种控制可以让您在迭代期间删除递归 CTE 的行。

但是，您可以在递归 CTE 完成后将它们过滤掉，也许在仅考虑最后有意义的行的辅助 CTE 上（通过某种要定义的规则）。

在 PostgreSQL 中可以找到唯一模糊相似的想法，您可以在其中使用该UNION子句除了UNION ALL，以避免产生更多相同的行。但无论如何，这与您需要的不同。

推荐阅读