如何用单条SQL语句删除两组表中键值重复的行

可以用单条SQL语句实现，具体语法取决于你使用的数据库，以下是针对主流数据库的实现方案：

MySQL（8.0+） #

方式1：批量删除（单条语句包含多个DELETE操作） #

通过INTERSECT获取两组表中共同的first_name，再用IN子查询批量删除对应行：

DELETE FROM t11 WHERE first_name IN (
    SELECT first_name FROM (
        SELECT first_name FROM t11 UNION ALL SELECT first_name FROM t12 UNION ALL SELECT first_name FROM t13 UNION ALL SELECT first_name FROM t14
    ) t1_all
    INTERSECT
    SELECT first_name FROM (
        SELECT first_name FROM t21 UNION ALL SELECT first_name FROM t22 UNION ALL SELECT first_name FROM t23 UNION ALL SELECT first_name FROM t24
    ) t2_all
);
DELETE FROM t12 WHERE first_name IN (SELECT first_name FROM (SELECT first_name FROM t11 UNION ALL SELECT first_name FROM t12 UNION ALL SELECT first_name FROM t13 UNION ALL SELECT first_name FROM t14) t1_all INTERSECT SELECT first_name FROM (SELECT first_name FROM t21 UNION ALL SELECT first_name FROM t22 UNION ALL SELECT first_name FROM t23 UNION ALL SELECT first_name FROM t24) t2_all);
DELETE FROM t13 WHERE first_name IN (SELECT first_name FROM (SELECT first_name FROM t11 UNION ALL SELECT first_name FROM t12 UNION ALL SELECT first_name FROM t13 UNION ALL SELECT first_name FROM t14) t1_all INTERSECT SELECT first_name FROM (SELECT first_name FROM t21 UNION ALL SELECT first_name FROM t22 UNION ALL SELECT first_name FROM t23 UNION ALL SELECT first_name FROM t24) t2_all);
DELETE FROM t14 WHERE first_name IN (SELECT first_name FROM (SELECT first_name FROM t11 UNION ALL SELECT first_name FROM t12 UNION ALL SELECT first_name FROM t13 UNION ALL SELECT first_name FROM t14) t1_all INTERSECT SELECT first_name FROM (SELECT first_name FROM t21 UNION ALL SELECT first_name FROM t22 UNION ALL SELECT first_name FROM t23 UNION ALL SELECT first_name FROM t24) t2_all);
DELETE FROM t21 WHERE first_name IN (SELECT first_name FROM (SELECT first_name FROM t11 UNION ALL SELECT first_name FROM t12 UNION ALL SELECT first_name FROM t13 UNION ALL SELECT first_name FROM t14) t1_all INTERSECT SELECT first_name FROM (SELECT first_name FROM t21 UNION ALL SELECT first_name FROM t22 UNION ALL SELECT first_name FROM t23 UNION ALL SELECT first_name FROM t24) t2_all);
DELETE FROM t22 WHERE first_name IN (SELECT first_name FROM (SELECT first_name FROM t11 UNION ALL SELECT first_name FROM t12 UNION ALL SELECT first_name FROM t13 UNION ALL SELECT first_name FROM t14) t1_all INTERSECT SELECT first_name FROM (SELECT first_name FROM t21 UNION ALL SELECT first_name FROM t22 UNION ALL SELECT first_name FROM t23 UNION ALL SELECT first_name FROM t24) t2_all);
DELETE FROM t23 WHERE first_name IN (SELECT first_name FROM (SELECT first_name FROM t11 UNION ALL SELECT first_name FROM t12 UNION ALL SELECT first_name FROM t13 UNION ALL SELECT first_name FROM t14) t1_all INTERSECT SELECT first_name FROM (SELECT first_name FROM t21 UNION ALL SELECT first_name FROM t22 UNION ALL SELECT first_name FROM t23 UNION ALL SELECT first_name FROM t24) t2_all);
DELETE FROM t24 WHERE first_name IN (SELECT first_name FROM (SELECT first_name FROM t11 UNION ALL SELECT first_name FROM t12 UNION ALL SELECT first_name FROM t13 UNION ALL SELECT first_name FROM t14) t1_all INTERSECT SELECT first_name FROM (SELECT first_name FROM t21 UNION ALL SELECT first_name FROM t22 UNION ALL SELECT first_name FROM t23 UNION ALL SELECT first_name FROM t24) t2_all);

方式2：严格单条语句（无分号分隔） #

通过LEFT JOIN关联所有表和共同first_name集合，一次性删除匹配行：

DELETE t11, t12, t13, t14, t21, t22, t23, t24
FROM (
    SELECT first_name FROM (
        SELECT first_name FROM t11 UNION ALL SELECT first_name FROM t12 UNION ALL SELECT first_name FROM t13 UNION ALL SELECT first_name FROM t14
    ) t1_all
    INTERSECT
    SELECT first_name FROM (
        SELECT first_name FROM t21 UNION ALL SELECT first_name FROM t22 UNION ALL SELECT first_name FROM t23 UNION ALL SELECT first_name FROM t24
    ) t2_all
) AS common_names
LEFT JOIN t11 ON t11.first_name = common_names.first_name
LEFT JOIN t12 ON t12.first_name = common_names.first_name
LEFT JOIN t13 ON t13.first_name = common_names.first_name
LEFT JOIN t14 ON t14.first_name = common_names.first_name
LEFT JOIN t21 ON t21.first_name = common_names.first_name
LEFT JOIN t22 ON t22.first_name = common_names.first_name
LEFT JOIN t23 ON t23.first_name = common_names.first_name
LEFT JOIN t24 ON t24.first_name = common_names.first_name;

PostgreSQL #

使用WITH子句预先定义共同的first_name集合，链式执行所有表的删除操作：

WITH common_names AS (
    SELECT first_name FROM (
        SELECT first_name FROM t11 UNION ALL
        SELECT first_name FROM t12 UNION ALL
        SELECT first_name FROM t13 UNION ALL
        SELECT first_name FROM t14
    ) t1_all
    INTERSECT
    SELECT first_name FROM (
        SELECT first_name FROM t21 UNION ALL
        SELECT first_name FROM t22 UNION ALL
        SELECT first_name FROM t23 UNION ALL
        SELECT first_name FROM t24
    ) t2_all
),
del_t11 AS (DELETE FROM t11 WHERE first_name IN (SELECT * FROM common_names)),
del_t12 AS (DELETE FROM t12 WHERE first_name IN (SELECT * FROM common_names)),
del_t13 AS (DELETE FROM t13 WHERE first_name IN (SELECT * FROM common_names)),
del_t14 AS (DELETE FROM t14 WHERE first_name IN (SELECT * FROM common_names)),
del_t21 AS (DELETE FROM t21 WHERE first_name IN (SELECT * FROM common_names)),
del_t22 AS (DELETE FROM t22 WHERE first_name IN (SELECT * FROM common_names)),
del_t23 AS (DELETE FROM t23 WHERE first_name IN (SELECT * FROM common_names))
DELETE FROM t24 WHERE first_name IN (SELECT * FROM common_names);

内容的提问来源于stack exchange，提问作者Yanxin Xiang