如何根据id列的重复出现次数更新对应Occurrence列的值

方案1：直接关联聚合子查询（通用写法，兼容绝大多数数据库） #

你原本的思路可以简化为无需创建临时表，直接关联GROUP BY的聚合结果更新：

-- 请将your_table替换为你实际的表名
UPDATE your_table t1
INNER JOIN (
    SELECT id, COUNT(*) AS occur_count
    FROM your_table
    GROUP BY id
) t2 ON t1.id = t2.id
SET t1.Occurrence = t2.occur_count;

如果不需要统计ID为NULL的行，可以在子查询的FROM your_table后添加WHERE id IS NOT NULL过滤。

方案2：临时表方案（适合百万级以上大数据量场景） #

和你尝试的思路一致，先将统计结果存入临时表再关联更新，性能更稳定：

• 第一步：创建临时表存储每个ID的出现次数

CREATE TEMPORARY TABLE temp_id_count (
    id INT PRIMARY KEY,
    cnt INT
) AS
SELECT id, COUNT(*) AS cnt
FROM your_table
GROUP BY id;

• 第二步：关联临时表更新原表

UPDATE your_table t
INNER JOIN temp_id_count c ON t.id = c.id
SET t.Occurrence = c.cnt;

• 第三步：可选删除临时表（会话结束后数据库会自动回收临时表）

DROP TEMPORARY TABLE IF EXISTS temp_id_count;

方案3：窗口函数写法（兼容MySQL8.0+/PostgreSQL/SQL Server等新数据库） #

如果你的数据库支持窗口函数，也可以用以下更简洁的写法：

WITH id_occur_stats AS (
    SELECT id, COUNT(*) OVER(PARTITION BY id) AS occur_cnt
    FROM your_table
)
UPDATE your_table t
INNER JOIN id_occur_stats s ON t.id = s.id
SET t.Occurrence = s.occur_cnt;

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