萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL 查找多字段完全相同的記錄:自連接 + 不等條件去重

需求:在 movestar(id, name, title, address) 表中,找出 title 和 address 完全相同 的不同人員組合。

測試數據

movestar 表數據

核心 SQL


SELECT star1.name, star2.name, star1.title, star1.address
FROM movestar AS star1, movestar AS star2
WHERE star1.title = star2.title
  AND star1.address = star2.address
  AND star1.name < star2.name;  -- 關鍵:用 < 避免重複對 (A,B) 和 (B,A)

結果

查詢結果

關鍵點解析

  1. 自連接: 表別名 star1/star2 代表同一表的兩個實例,兩兩配對比較;
  2. 多字段匹配: title 和 address 同時相等;
  3. 去重關鍵 star1.name < star2.name:
    • 若用 <>,會產生 (A,B) 和 (B,A) 兩行重複;
    • < 強制字典序較小的在前,每對只出現一次;
    • 前提:name 唯一或作為業務鍵可排序。

擴展:現代寫法(窗口函數)


SELECT name, title, address
FROM (
    SELECT *, 
           ROW_NUMBER() OVER (PARTITION BY title, address ORDER BY name) AS rn,
           COUNT(*) OVER (PARTITION BY title, address) AS cnt
    FROM movestar
) t
WHERE cnt > 1 AND rn = 1;  -- 每組取第一個代表

窗口函數寫法更直觀、擴展性強(易於取每組前 N、聚合統計等),推薦用於 SQL Server 2005+、PostgreSQL、MySQL 8.0+ 環境。

copyright © 萬盛學電腦網 all rights reserved