SQL Server 查詢「兩列值完全相同且至少出現 3 次」的記錄技巧

業務場景

表 #T1 包含列 A、B，需找出 (A, B) 組合完全相同且出現次數 ≥ 3的所有行。

解決方案：關聯子查詢 + 條件聚合

SELECT *
FROM #T1 t1
WHERE (
    -- 該 A 值下，去重後的 (A,B) 組合數 = 1（即 B 固定）
    SELECT COUNT(*)
    FROM (SELECT DISTINCT A, B FROM #T1 WHERE A = t1.A) d
) = 1
AND (
    -- 該 A 值下，總行數 ≥ 3
    SELECT COUNT(DISTINCT ID) FROM #T1 WHERE A = t1.A
) >= 3
ORDER BY t1.A;

邏輯拆解

1. SELECT DISTINCT A, B FROM #T1 WHERE A = t1.A：針對外層當前行的 A 值，取所有 (A,B) 去重組合。
2. 外層 COUNT(*) = 1：確保該 A 下 B 值唯一（即所有行 B 相同）。
3. COUNT(DISTINCT ID) >= 3：該 A 分組至少 3 行（假設 ID 為主鍵）。

更高效寫法（窗口函數，SQL Server 2012+）

WITH cte AS (
    SELECT *,
        COUNT(*) OVER (PARTITION BY A, B) AS ab_cnt,
        COUNT(*) OVER (PARTITION BY A) AS a_cnt
    FROM #T1
)
SELECT A, B, 
FROM cte
WHERE ab_cnt = a_cnt  -- 該 A 下所有行 B 相同
  AND a_cnt >= 3;     -- 至少 3 行

優勢：單次掃描、無關聯子查詢、利用索引 (A, B) 极快。

索引建議

創建非聚集索引 CREATE INDEX IX_T1_A_B ON #T1(A, B) INCLUDE (ID) 覆蓋查詢，避免鍵查找。