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

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)

star1/star2 代表同一表的兩個實例,兩兩配對比較;title 和 address 同時相等;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+ 環境。