SQL 更新列為某值出現次數：窗口函數與連接更新兩種方案

需求：表 Test 中，將 Num 列更新為同組 Col_A 值出現的次數。例如 Col_A=123 出現 2 次，則該組兩行 Num 均更新為 2。本文提供 MySQL 8.0+ 窗口函數方案與通用連接更新方案。

測試數據

CREATE TABLE Test (Col_A INT, Num INT);
INSERT INTO Test VALUES (123,0), (123,0), (124,0), (125,0);
-- 期望：123→2, 124→1, 125→1

方案一：窗口函數（MySQL 8.0、PostgreSQL、SQL Server、Oracle）

WITH Counts AS (
    SELECT Col_A, COUNT(*) AS cnt
    FROM Test
    GROUP BY Col_A
)
UPDATE Test t
JOIN Counts c ON t.Col_A = c.Col_A
SET t.Num = c.cnt;

或使用窗口函數直接更新（部分數據庫支持）：

UPDATE Test
SET Num = cnt
FROM (
    SELECT Col_A, COUNT(*) OVER (PARTITION BY Col_A) AS cnt
    FROM Test
) AS sub
WHERE Test.Col_A = sub.Col_A;

方案二：衍生表連接（通用，MySQL 5.7 等）

UPDATE Test t
INNER JOIN (
    SELECT Col_A, COUNT(*) AS cnt
    FROM Test
    GROUP BY Col_A
) AS agg ON t.Col_A = agg.Col_A
SET t.Num = agg.cnt;

這是最佳回答採用的方案，兼容性最好。

方案三：相關子查詢（簡單但性能較差）

UPDATE Test t
SET Num = (SELECT COUNT(*) FROM Test WHERE Col_A = t.Col_A);

每行執行一次子查詢，大表性能差，但語法最直觀。

驗證結果

SELECT * FROM Test ORDER BY Col_A;
-- Col_A | Num
-- 123   | 2
-- 123   | 2
-- 124   | 1
-- 125   | 1

擴展：按多列分組計數

UPDATE Test t
INNER JOIN (
    SELECT Col_A, Col_B, COUNT(*) AS cnt
    FROM Test
    GROUP BY Col_A, Col_B
) AS agg ON t.Col_A = agg.Col_A AND t.Col_B = agg.Col_B
SET t.Num = agg.cnt;

總結

• 首選 衍生表 INNER JOIN：標準 SQL、兼容性好、性能佳
• 數據庫支持窗口函數時可用 CTE + 窗口函數 更優雅
• 避免相關子查詢用於大表更新
• 更新前務必 SELECT 驗證聚合結果