在两列中找到副本

I have a table with four columns, where col1 & col2 contain similar values (INT). I now want to know if there are duplicates in col1 and/or col2. i.e.,

我有一个包含四列的表，其中col1和col2包含类似的值(INT)。我现在想知道col1和/或col2是否有重复。也就是说,

col1 | col2
-----+-----
111  | 222
333  | 444
111  | 333
555  | 111

→ Duplicates: 111 (3x) and 333 (2x).

→重复:111(3 x)和333(2 x)。

I am using SQLite, but I think this is a basic SQL question.

我正在使用SQLite，但我认为这是一个基本的SQL问题。

3 个解决方案

#1

To get a count of each element use UNION ALL in a subquery, then GROUP BY on the result of that:

要获取每个元素的计数，请在子查询中使用UNION ALL，然后对其结果进行分组:

SELECT col, COUNT(*)
FROM
(
    SELECT col1 AS col FROM Table1
    UNION ALL
    SELECT col2 FROM Table1
) T1
GROUP BY col

Add HAVING COUNT(*) > 1 if you only wish to see the duplicated values.

如果只希望看到重复的值，则添加COUNT(*) > 1。

#2

If you have two columns where the values are compatible (that is, 333 in one column represents the same "thing" as 333 in the other column) then this is called repeating groups. This is a violation of First Normal Form.

如果有两列的值是兼容的(即，一列中的333表示与另一列中的333相同的“东西”)，那么这就称为重复组。这违反了第一范式。

If the values were in a single column, finding duplicates would be easy:

如果值在一个列中，查找重复的值将很容易:

CREATE TABLE pairs (
  pair_id INT,
  value   INT,
  PRIMARY KEY (pair_id, value)
);

INSERT INTO pairs VALUES
(1, 111), (1, 222),
(2, 333), (2, 444),
(3, 111), (3, 333),
(4, 555), (4, 111);

SELECT value, COUNT(*)
FROM pairs
GROUP BY value
HAVING COUNT(*) > 1;

#3

-1

Join the table to itself to see if there are any duplicates.

将表连接到自己，看看是否有重复的内容。

select t1.col1, t2.col2
from table t1 inner join table t2 
  on t1.col1 = t2.col2

#1