使用GROUP BY函数按用户名与类型统计重复记录并填充Count列

原始数据表 #

目标结果表 #

实现SQL语句 #

通用关联更新法（适配多数数据库） #

通过子查询先统计每组记录数，再关联原表更新Count列：

UPDATE your_table t1
JOIN (
    SELECT USER_NAME, Type, COUNT(*) AS cnt
    FROM your_table
    GROUP BY USER_NAME, Type
) t2 ON t1.USER_NAME = t2.USER_NAME AND t1.Type = t2.Type
SET t1.Count = t2.cnt;

大小写不敏感场景适配 #

如果数据库对Type字段大小写敏感，需将Full和FULL视为同一分组，可修改为：

UPDATE your_table t1
JOIN (
    SELECT USER_NAME, LOWER(Type) AS lower_type, COUNT(*) AS cnt
    FROM your_table
    GROUP BY USER_NAME, LOWER(Type)
) t2 ON t1.USER_NAME = t2.USER_NAME AND LOWER(t1.Type) = t2.lower_type
SET t1.Count = t2.cnt;

窗口函数法（适用于MySQL 8.0+、PostgreSQL、SQL Server等） #

利用窗口函数直接计算每行对应的分组计数：

WITH group_counts AS (
    SELECT USER_ID, COUNT(*) OVER (PARTITION BY USER_NAME, Type) AS cnt
    FROM your_table
)
UPDATE your_table t
SET t.Count = gc.cnt
FROM group_counts gc
WHERE t.USER_ID = gc.USER_ID;

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