如何高效检查表中所有列的重复行？

当表有数十列，手动指定所有列名太麻烦时，试试以下几种可行方案：

方案1：借助数据库元数据自动生成列名 #

不同数据库获取表列名的方式不同，先查列名再拼接SQL：

MySQL/MariaDB #

先获取目标表的所有列名：

SELECT GROUP_CONCAT(COLUMN_NAME SEPARATOR ', ')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = '你的数据库名' AND TABLE_NAME = 'mytable';

把查询结果替换到下面的GROUP BY子句中，就能得到完整的查重SQL：

SELECT *, COUNT(*) AS duplicate_count
FROM mytable
GROUP BY [上面查询得到的列名列表]
HAVING COUNT(*) > 1;

PostgreSQL #

获取列名：

SELECT string_agg(column_name, ', ')
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'mytable';

替换后生成最终SQL，逻辑和MySQL一致。

SQL Server #

获取列名：

SELECT STRING_AGG(COLUMN_NAME, ', ')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'mytable' AND TABLE_SCHEMA = 'dbo';

同样替换到GROUP BY子句即可。

方案2：用窗口函数查看所有重复行的具体内容 #

如果需要看到每一行重复的完整数据（而非分组汇总），用ROW_NUMBER()窗口函数：

SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY [所有列名] ORDER BY (SELECT NULL)) AS rn
    FROM mytable
) t
WHERE rn > 1;

这里的PARTITION BY同样用元数据查询得到的列名列表替换，就能列出所有重复的行，包括每行的细节。

方案3：SQL Server专属简化写法 #

SQL Server支持GROUP BY ALL语法，可直接写：

SELECT *, COUNT(*) AS duplicate_count
FROM mytable
GROUP BY ALL *
HAVING COUNT(*) > 1;

注意：这个语法仅适用于SQL Server，其他数据库不支持。

内容的提问来源于stack exchange，提问作者יואב שובב