SQL技术问询：如何统计相似行数并新增列？select distinct未达预期

嘿，我懂你想要实现的需求——给数据表新增一列，用来显示每一行对应的相似重复行的总数对吧？SELECT DISTINCT确实搞不定这个，因为它只是帮你去重展示数据，没法统计每组重复行的数量。下面给你几种通用的解决方案，适配大部分主流SQL数据库：

方法一：用窗口函数（推荐，支持现代数据库） #

如果你的数据库支持窗口函数（比如MySQL 8.0+、PostgreSQL、SQL Server、Oracle等），这是最简洁高效的方式：

SELECT 
    *,
    -- 把col1、col2、col3替换成你判断"相似行"的列
    COUNT(*) OVER (PARTITION BY col1, col2, col3) AS duplicate_count
FROM your_table;

说明： #

• PARTITION BY后面的列就是你用来定义“相似”的依据——只要这些列的值完全相同，就会被归为一组。
• COUNT(*) OVER (...)会为每组的每一行都返回该组的总行数，也就是这一行的重复次数（包括自身）。

方法二：分组聚合+关联（适配旧版数据库） #

如果你的数据库不支持窗口函数（比如MySQL 5.x），可以先通过子查询统计每组的重复数，再和原表关联：

SELECT 
    t.*,
    g.duplicate_count
FROM your_table t
INNER JOIN (
    -- 同样替换成你的判断列
    SELECT col1, col2, col3, COUNT(*) AS duplicate_count
    FROM your_table
    GROUP BY col1, col2, col3
) g 
ON t.col1 = g.col1 
AND t.col2 = g.col2 
AND t.col3 = g.col3;

可选：创建永久的重复数列 #

如果你希望这个重复数列成为表的固定列（数据变化时自动更新），可以用数据库的生成列功能（以MySQL为例）：

ALTER TABLE your_table
ADD COLUMN duplicate_count INT 
GENERATED ALWAYS AS (
    (SELECT COUNT(*) FROM your_table t2 
     WHERE t2.col1 = your_table.col1 
       AND t2.col2 = your_table.col2 
       AND t2.col3 = your_table.col3)
) STORED;

之后如果想查看这个新增列的信息，执行SHOW COLUMNS FROM your_table;就能看到duplicate_count列的详情了，这也对应你提到的show column of number of duplicates需求。

注意事项： #

• 一定要把示例中的col1, col2, col3替换成你实际用来判定“相似行”的列，比如只需要某一列重复就计数，就只保留那一个列即可。
• 如果统计时不需要包含自身，可以把COUNT(*)改成COUNT(*)-1，得到的就是排除当前行的重复数量。

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