识别组合列中的重复数据

Question

表中有Lang1 (第一语言ID)和Lang2 (第二语言ID)，它们在逻辑上重复，我想保留一条记录并删除另一条记录。我用不同的颜色包围了这些条目。例如，“红色颜色”语言集为(4和6)，还有另一组语言集，即(6和4)。我们怎么能认为这是一个重复的例子。请帮帮忙。

Answer 1

您可以这样做(下面的所有代码都可以在这里上使用)：

CREATE TABLE test
(
  lpo_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  lang_1 INTEGER NOT NULL,
  lang_2 INTEGER NOT NULL,
  
  CONSTRAINT l1_ne_l2_ck CHECK (lang_1 != lang_2)
);

填充它：

INSERT INTO test (lang_1, lang_2) VALUES
(6, 4), (4, 6), (6, 68), (68, 6), (18, 6), (6, 18), (36, 18), (18, 36), 
(1, 2), (1, 3), (1, 4), (1, 5), (1, 6), (1, 7), (1, 8), 
(2, 3), (2, 4), (2, 5), (2, 6), (2, 7), (2, 8);

请注意，INSERTs的第一行包含重复的内容。现在我们使用LEAST()和GREATEST()函数，如下所示：

SELECT 
  lpo_id,
  LEAST(lang_1, lang_2), 
  GREATEST(lang_1, lang_2)
FROM test ORDER BY lpo_id;

结果：

lpo_id  LEAST(lang_1, lang_2)   GREATEST(lang_1, lang_2)
1               4                        6
2               4                        6
3               6                       68
4               6                       68
5               6                       18
6               6                       18
7              18                       36
8              18                       36
9               1                        2
...
... remaining non-duplicates snipped for brevity
...

所以，现在我们将ROW_NUMBER()添加到混合中。窗口函数(MariaDB文档这里)非常强大，它将回报您多次学习它们所付出的一切努力！

SELECT 
  lpo_id,
  LEAST(lang_1, lang_2),
  GREATEST(lang_1, lang_2),
  ROW_NUMBER() 
    OVER (PARTITION BY LEAST(lang_1, lang_2), GREATEST(lang_1, lang_2)) AS rn
FROM test
ORDER BY lpo_id;

结果：

lpo_id  LEAST(lang_1, lang_2)   GREATEST(lang_1, lang_2)    rn
1                  4                         6               1
2                  4                         6               2
3                  6                        68               1
4                  6                        68               2
5                  6                        18               1
6                  6                        18               2
7                 18                        36               1
8                 18                        36               2
9                  1                         2               1
...
... non-dups snipped for brevity
...

然后，我们使用DELETE，lpo_ids，它的rn >1，如下：

DELETE FROM test
WHERE test.lpo_id IN
(
  SELECT tab.lpo_id FROM
  (
    SELECT 
      t.lpo_id, LEAST(t.lang_1, t.lang_2), GREATEST(t.lang_1, t.lang_2), 
      ROW_NUMBER() OVER (PARTITION BY LEAST(t.lang_1, t.lang_2), GREATEST(t.lang_1, t.lang_2)) AS rn
    FROM test t ORDER BY t.lpo_id
  ) AS tab
  WHERE tab.rn > 1
);

结果是在db<>fiddle上出现了一个勾标.

然后，检查：

 SELECT * FROM test ORDER BY test.lpo_id;

结果：

lpo_id  lang_1  lang_2
1          6         4
3          6        68
6          6        18
7         36        18
9          1         2
10         1         3
...
... other non-dups snipped for brevity
...

您也可以使用带有字符串的LEAST和GREATEST函数--参见小提琴这里 --改编自您自己的函数！

重要建议：

您还可以轻微地更改表定义，这样就不会使用以下CHECK约束再次发生这种情况：

CREATE TABLE langue
(
  id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ipid INTEGER DEFAULT NULL,
  lang_1 VARCHAR (200) DEFAULT NULL,
  lang_2 VARCHAR (200) DEFAULT NULL,
  approved INTEGER NOT NULL,
  
  CONSTRAINT l1_ne_l2_ck CHECK (lang_1 != lang_2),
  CONSTRAINT l1_lt_l2_ck CHECK (lang_1 < lang_2)
);

因此，我们有两个约束-- lang_1不能等于lang_2，lang_1必须比lang_2“小”-- <运算符处理字符串和数字。

所以，现在，如果我们试图做以下工作：

INSERT INTO langue VALUES (1, 69, 'French', 'English', 0);

我们抛出一个错误：

CONSTRAINT `l1_lt_l2_ck` failed for `db_334239447`.`langue`

但如果我们做相反的事情：

INSERT INTO langue VALUES (1, 69, 'English', 'French', 0);

我们成功了！