mysql - 如何删除重复项

Question

我有两张 table 测试和测试 1

测试

id formula 
1  A12+C32+D+X
2  K/Y

测试1

   id Code 
    6  A12
    7  C32
   100 A1
    10 D
    12 X
    13 K
    14 Y

如何更新表格测试中的公式(文本格式)以获取

id formula 
    1  [6]+[7]+[10]+[12]
    2  [13]/[14]

尝试以下脚本 sqlfield但它不会重新运行正确的结果。它返回

 RFORMULA
[6]+C32+D+X//need to remove it 
[13]/Y//need to remove it 
[13]/[14]//the best result 
[100]2+[7]+[10]+[12]
[13]/Y//need to remove it 
K/[14]//need to remove it 
[6]+[7]+[10]+[12]//the best result 

我正在研究它几个小时，知道吗？

Answer 1

尝试使用以下方法:

SQL Fiddle

WITH a AS
  ( SELECT DISTINCT A.id group_id,
    Split.A.value('.', 'VARCHAR(100)') AS Data
  FROM
    (SELECT id,
      CAST ('<M>' + REPLACE(REPLACE(REPLACE(REPLACE(formula, '/', '/</M><M>'), '+','+</M><M>'), '-','+</M><M>'),'*','+</M><M>') + '</M>' AS XML) AS Data
    FROM test
    ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a)
  ),
  b AS
  (SELECT group_id,
    REPLACE(LEFT(Data, LEN(Data)-1), t.[Code], concat(t.[id],RIGHT(Data,1))) yy,
    REPLACE(Data, t.[Code], t.[id])uu
  FROM a
  LEFT JOIN test1 t
  ON t.Code =LEFT(Data, LEN(Data)-1)
  OR (t.Code=Data)
  )


SELECT group_id,
  REPLACE(REPLACE(REPLACE(REPLACE(stuff(
  (SELECT uu FROM b WHERE newtable.group_id=group_id FOR XML PATH('')
  ), 1,1,''), 'uu', ''), '</>', ''),'<', ''), '>', '')
FROM b newtable
GROUP BY group_id

在这里，我还指定了乘法和减法，以防它们出现在您的表达式列中。如果存在其他的，您可以相应地编辑它:

CAST ('<M>' + REPLACE(REPLACE(REPLACE(REPLACE(formula, '/', '/</M><M>'), '+','+</M><M>'), '-','+</M><M>'),'*','+</M><M>') + '</M>' AS XML) AS Data