如何在一个表中获得重复的记录，而不是在其他表中?

Question

表1和表2如下:

Table1:

col1  col2
-------------
a1    b1
a2    b1
a3    b2
a4    b3
a5    b3
a5    b4
a5    b2

表:

col2  col3
----------
b1    c1
b4    c2

为了获得表1中col2的所有重复条目，我编写了以下查询:

SELECT x.col1,x.col2
FROM table1 x
JOIN (SELECT t.col2
FROM table1 t
GROUP BY t.col2
HAVING COUNT(t.col2) > 1) y ON y.col2 = x.col2

现在我想从上面的结果中删除表2

中的条目预期输出:

col1  col2
----------
a3    b2
a4    b3
a5    b3
a5    b2 

Query I write:

SELECT x.col1,x.col2
FROM table1 x
JOIN (SELECT t.col2
FROM table1 t
GROUP BY t.col2
HAVING COUNT(t.col2) > 1) y ON y.col2 = x.col2 where x.col2 not in (select col2 from table2)

我看到使用上述查询的预期结果。是否有更有效的方法来达到同样的结果?和有什么情况下,我可能会错过吗?

感谢

Answer 1

这个脚本从表2中留下b4，因为col2中的b4不是表1中col2的重复。

DROP TABLE IF EXISTS Table1
DROP TABLE IF EXISTS Table2
CREATE TABLE Table1
(
col1 VARCHAR(10),
col2 VARCHAR(10)
)
GO
CREATE TABLE Table2
(
col2 VARCHAR(10),
col3 VARCHAR(10)
)
GO
INSERT INTO Table1
VALUES
('a1', 'b1'),
('a2', 'b1'),
('a3', 'b2'),
('a4', 'b3'),
('a5', 'b3'),
('a5', 'b4'),
('a5', 'b2')
INSERT INTO Table2
VALUES
('b1', 'c1'),
('b4', 'c2')
SELECT T2.*
FROM Table2 T2
LEFT JOIN
(
SELECT col2
FROM Table1
GROUP BY col2
HAVING COUNT(*) > 1
) T1 ON T1.col2 = T2.col2
WHERE T1.col2 IS NULL

Answer 2

根据您实际使用的DBMS，您可以使用窗口函数和存在表达式。

SELECT
*
FROM
(
SELECT
*,
COUNT(*) OVER (PARTITION BY col2) AS occurences
FROM
table1
)
t1
WHERE
occurrences > 1
AND
NOT EXISTS (
SELECT *
FROM table2
WHERE col2 = t1.col2
)

Answer 3

如果您的DBMS支持CTE, Common Table Expressions，您可以尝试以下操作:

with cte as (
select col1,col2, count(*) over (partition by col2) as cn from Table1
)
select T.col1,T.col2 from cte T
left join Table2 D on T.col2=D.col2
where T.cn>1 and D.col2 is null

从这里看到一个演示。

适用于MySQL 8.0及以上和PostgreSQL。