SQL统计重复行出现次数、去重并新增行计数列的实现方法

通用SQL实现方案 #

方案1：直接生成包含统计结果的查询结果 #

如果你只需要拿到去重加计数的结果集，不需要修改原表，直接用GROUP BY加聚合函数即可：

SELECT 
    name,
    country,
    price,
    COUNT(*) AS count
FROM 你的原表名
GROUP BY name, country, price;

注意：如果你的原结果集是临时查询出来的，把上面的你的原表名替换成你的子查询语句即可，比如你之前的查询是SELECT name,country,price FROM 业务表 WHERE xxx，替换后写法如下：

SELECT 
    name,
    country,
    price,
    COUNT(*) AS count
FROM (
    SELECT name,country,price FROM 业务表 WHERE xxx -- 替换成你原来的查询语句
) AS t
GROUP BY name, country, price;

方案2：修改原表实现去重+新增计数列 #

如果你需要把原表修改成去重加计数的结构，分三步操作：

1. 先给原表新增count列

ALTER TABLE 你的原表名 ADD COLUMN count INT;

2. 把重复行的计数更新到所有对应行

UPDATE 你的原表名 t1
JOIN (
    SELECT name, country, price, COUNT(*) AS cnt
    FROM 你的原表名
    GROUP BY name, country, price
) t2 ON t1.name = t2.name AND t1.country = t2.country AND t1.price = t2.price
SET t1.count = t2.cnt;

3. 删除重复行，只保留每一组的第一条

-- MySQL 8.0+/PostgreSQL/Oracle等支持窗口函数的数据库可以用这个写法
DELETE FROM 你的原表名
WHERE (name, country, price, id) IN (
    SELECT name, country, price, id
    FROM (
        SELECT 
            id,
            ROW_NUMBER() OVER (PARTITION BY name, country, price ORDER BY id) AS rn
        FROM 你的原表名
    ) t
    WHERE rn > 1
);

如果你用的数据库不支持窗口函数，也可以用临时表中转的方式实现，先把去重后的数据存到临时表，清空原表再插回即可，这种方式性能更高，尤其适合你10万行量级的数据，处理速度比逐行删除快很多：

CREATE TEMPORARY TABLE temp_table AS
SELECT name, country, price, COUNT(*) AS count
FROM 你的原表名
GROUP BY name, country, price;

TRUNCATE TABLE 你的原表名;

INSERT INTO 你的原表名 (name, country, price, count)
SELECT name, country, price, count FROM temp_table;

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