小贝子编程

使用递归的字符串替换在SQL中不起作用(字符串的第一部分没有被替换，其余部分都很好)



我有下面的代码，我试图用数值替换字符串。基于按功能分组。（根据一个条件多次替换单个字符串）。

它运行良好，但字符串的第一部分没有被替换，其余部分运行良好。我不知道这个问题到底发生在哪里。

有人能在这方面帮我吗？

CREATE TABLE #MathTemp1
(
IDNUM INTEGER IDENTITY(1,1),
YEARMONTH VARCHAR(256),
OUTPUTFORMULA VARCHAR(256)
)
CREATE TABLE #MathLogicTable
(
IDNUM INTEGER,
FORMULA Varchar(256),
INPUTName varchar(160),
AttributeValue Decimal(15,3),
yearmonth varchar(160)
)
INSERT INTO #MathLogicTable ([IDNUM], [formula], [INPUTNAME], [AttributeValue], [YEARMONTH])
VALUES ('1', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 1 ', '17.000', '2013')
    ,('2', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 2','25.000' ,'2013')
    ,('3', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 3','12.000' ,'2013')
    ,('4', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 4','13.000' ,'2013')
  ,('5', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 5','11.000' ,'2013')

  ,('6', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 6','10.000' ,'2013')
     ,('7', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 7','21.000' ,'2013')
    ,('8', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 8','26.000' ,'2013')
    ,('9', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 9','23.000' ,'2013')   
  ,('10', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 10','27.000' ,'2013')
  ,('11', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 11','51.000' ,'2013')
  ,('12', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 12','46.000' ,'2013')
  ,('1', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 1 ', '17.000', '2014')
    ,('2', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 2','25.000' ,'2014')
    ,('3', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 3','12.000' ,'2014')
    ,('4', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 4','13.000' ,'2014')
  ,('5', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 5','11.000' ,'2014')

  ,('6', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 6','10.000' ,'2014')
    ,('7', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 7','21.000' ,'2014')
    ,('8', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 8','26.000' ,'2014')
    ,('9', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 9','23.000' ,'2014')   
  ,('10', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 10','27.000' ,'2014')
  ,('11', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 11','51.000' ,'2014')
  ,('12', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 12','46.000' ,'2014')


Select * from #MathLogicTable
;WITH DataSource1 AS
( 
    SELECT ROW_NUMBER() OVER(PARTITION BY [YEARMONTH] ORDER BY [IDNUM]) AS [ReplacementOrderID]
      ,[YEARMONTH]
      ,[formula]
      ,[INPUTNAME] AS [ReplacementString]
      ,AttributeValue AS [ReplacementValue]
    FROM #MathLogicTable
)
,RecursiveDataSource AS
(
    SELECT [ReplacementOrderID]
      ,[YEARMONTH]
      ,REPLACE([formula], [ReplacementString], [ReplacementValue]) AS [formula]
    FROM DataSource1 
    WHERE [ReplacementOrderID] = 1
    UNION ALL
    SELECT DS.[ReplacementOrderID]
      ,DS.[YEARMONTH]
      ,REPLACE(RDS.[formula], DS.[ReplacementString], DS.[ReplacementValue]) AS [formula]
    FROM RecursiveDataSource RDS
    INNER JOIN DataSource1 DS
    ON RDS.[ReplacementOrderID] + 1 = DS.[ReplacementOrderID]
    AND RDS.[YEARMONTH] = DS.[YEARMONTH]
)
INSERT INTO #MathTemp1
SELECT RDS.[YEARMONTH]
    ,RDS.[formula]
FROM RecursiveDataSource RDS
INNER JOIN 
(
    SELECT [YEARMONTH]
      ,MAX([ReplacementOrderID]) AS [ReplacementOrderID]
    FROM DataSource1 
    GROUP BY [YEARMONTH]
) DS
    ON RDS.[YEARMONTH] = DS.[YEARMONTH]
    AND RDS.[ReplacementOrderID] = DS.[ReplacementOrderID]
ORDER BY RDS.[YEARMONTH]
option (maxrecursion 0);
SELECT * FROM #MathTemp1
--DROP TABLE #MathLogicTable
--DROP TABLE #MathTemp1

我得到的输出显示在下面

IDNUM  YEARMONTH        OUTPUTFORMULA
1       2013        ( (CV3: 1) + (25.000) + (12.000) + (13.000) + (11.000) + (10.000) + (21.000) + (26.000) + (23.000) + (27.000) + (51.000) + (46.000) )/1000000
2       2014        ( (CV3: 1) + (25.000) + (12.000) + (13.000) + (11.000) + (10.000) + (21.000) + (26.000) + (23.000) + (27.000) + (51.000) + (46.000) )/1000000

但所需的输出如下所示。问题是输出公式中的第一个字符串没有被替换。

 IDNUM  YEARMONTH        OUTPUTFORMULA
1       2013        ( (17.000) + (25.000) + (12.000) + (13.000) + (11.000) + (10.000) + (21.000) + (26.000) + (23.000) + (27.000) + (51.000) + (46.000) )/1000000
2       2014        ( (17.000) + (25.000) + (12.000) + (13.000) + (11.000) + (10.000) + (21.000) + (26.000) + (23.000) + (27.000) + (51.000) + (46.000) )/1000000

问题是，在创建表时，对于InputNAME列，'CV3: 1 '中有一个空间，而它应该是'CV3: 1'。

CREATE TABLE #MathTemp1
(
IDNUM INTEGER IDENTITY(1,1),
YEARMONTH VARCHAR(256),
OUTPUTFORMULA VARCHAR(256)
)
CREATE TABLE #MathLogicTable
(
IDNUM INTEGER,
FORMULA Varchar(256),
INPUTName varchar(160),
AttributeValue Decimal(15,3),
yearmonth varchar(160)
)
INSERT INTO #MathLogicTable ([IDNUM], [formula], [INPUTNAME], [AttributeValue], [YEARMONTH])
VALUES ('1', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 1', '17.000', '2013')
    ,('2', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 2','25.000' ,'2013')
    ,('3', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 3','12.000' ,'2013')
    ,('4', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 4','13.000' ,'2013')
  ,('5', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 5','11.000' ,'2013')

  ,('6', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 6','10.000' ,'2013')
     ,('7', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 7','21.000' ,'2013')
    ,('8', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 8','26.000' ,'2013')
    ,('9', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 9','23.000' ,'2013')   
  ,('10', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 10','27.000' ,'2013')
  ,('11', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 11','51.000' ,'2013')
  ,('12', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 12','46.000' ,'2013')
  ,('1', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 1', '17.000', '2014')
    ,('2', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 2','25.000' ,'2014')
    ,('3', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 3','12.000' ,'2014')
    ,('4', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 4','13.000' ,'2014')
  ,('5', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 5','11.000' ,'2014')

  ,('6', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 6','10.000' ,'2014')
    ,('7', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 7','21.000' ,'2014')
    ,('8', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 8','26.000' ,'2014')
    ,('9', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 9','23.000' ,'2014')   
  ,('10', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 10','27.000' ,'2014')
  ,('11', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 11','51.000' ,'2014')
  ,('12', '( (CV3: 1) + (CV3: 2) + (CV3: 3) + (CV3: 4) + (CV3: 5) + (CV3: 6) + (CV3: 7) + (CV3: 8) + (CV3: 9) + (CV3: 10) + (CV3: 11) + (CV3: 12) )/1000000', 'CV3: 12','46.000' ,'2014')


Select * from #MathLogicTable
;WITH DataSource1 AS
( 
    SELECT ROW_NUMBER() OVER(PARTITION BY [YEARMONTH] ORDER BY [IDNUM]) AS [ReplacementOrderID]
      ,[YEARMONTH]
      ,[formula]
      ,[INPUTNAME] AS [ReplacementString]
      ,AttributeValue AS [ReplacementValue]
    FROM #MathLogicTable
)
,RecursiveDataSource AS
(
    SELECT [ReplacementOrderID]
      ,[YEARMONTH]
      ,REPLACE([formula], [ReplacementString], [ReplacementValue]) AS [formula]
    FROM DataSource1 
    WHERE [ReplacementOrderID] = 1
    UNION ALL
    SELECT DS.[ReplacementOrderID]
      ,DS.[YEARMONTH]
      ,REPLACE(RDS.[formula], DS.[ReplacementString], DS.[ReplacementValue]) AS [formula]
    FROM RecursiveDataSource RDS
    INNER JOIN DataSource1 DS
    ON RDS.[ReplacementOrderID] + 1 = DS.[ReplacementOrderID]
    AND RDS.[YEARMONTH] = DS.[YEARMONTH]
)
INSERT INTO #MathTemp1
SELECT RDS.[YEARMONTH]
    ,RDS.[formula]
FROM RecursiveDataSource RDS
INNER JOIN 
(
    SELECT [YEARMONTH]
      ,MAX([ReplacementOrderID]) AS [ReplacementOrderID]
    FROM DataSource1 
    GROUP BY [YEARMONTH]
) DS
    ON RDS.[YEARMONTH] = DS.[YEARMONTH]
    AND RDS.[ReplacementOrderID] = DS.[ReplacementOrderID]
ORDER BY RDS.[YEARMONTH]
option (maxrecursion 0);
SELECT * FROM #MathTemp1
--DROP TABLE #MathLogicTable
--DROP TABLE #MathTemp1

相关内容

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium