sqlite - sqlite3 将两个表字段合并为一个，去除重复项

Question

我有两张表需要合并为一张。表有不同的行数。

table1
      att1     rel1

      1954WM   1699AC, 1847BC


table-2
      att2     rel2

      1954WM   1699AC, 1331, 1649A
      1499DD   1891, 9951A

以上是结构的例子。我想要的是如果 table1.att1 == table-2.att2 那么 table1 看起来像这样：

table1
      att2     rel2

      1954WM   1699AC, 1847BC, 1331, 1649A
      1499DD   1891, 9951A

如果 table1.att1 中的 table-2.att2 不匹配，则将带有 rel2 的 table-2.att2 添加到 table1 中。

标签： sqlite

Answer 1

如果您不介意 rel1 中的重复项（即合并重复项是指 att? 列）并且如果att1 列具有 UNIQUE 约束，则：-

WITH a AS(SELECT att1,rel1 FROM `table1` UNION SELECT att2 AS att1,rel2 AS rel1 FROM `table-2`),
         b AS (SELECT att1,group_concat(rel1) AS rel1 FROM a GROUP BY att1)          
INSERT OR REPLACE INTO `table1` SELECT att1,rel1 FROM b;

会导致

如果您不介意 rel1 中的重复项，但att1 列没有 UNIQUE 约束，则：-

WITH a AS(SELECT att1,rel1 FROM `table1` UNION SELECT att2 AS att1,rel2 AS rel1 FROM `table-2`),
         b AS (SELECT att1,group_concat(rel1) AS rel1 FROM a GROUP BY att1)          
INSERT INTO `table1` SELECT 'ADDED-'||att1,rel1 FROM b;

DELETE FROM `table1` WHERE instr(att1,'ADDED-') < 1;

UPDATE `table1` SET att1 = substr(att1,instr(att1,'ADDED-') + 1 + length('ADDED')); 

或者 ：-

CREATE UNIQUE INDEX IF NOT EXISTS att1ix ON `table1`(att1);
WITH a AS(SELECT att1,rel1 FROM `table1` UNION SELECT att2 AS att1,rel2 AS rel1 FROM `table-2`),
         b AS (SELECT att1,group_concat(rel1) AS rel1 FROM a GROUP BY att1)
INSERT OR REPLACE INTO `table1` SELECT att1,rel1 FROM b;

将提供相同的结果。