您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL 集合操作實戰：交集與差集在歌曲版權比對中的應用

編輯：關於SqlServer

業務場景：版權歌曲清單與本地曲庫比對

專案需求：比對 copyright_songs（版權曲庫）與 local_songs（本地使用曲庫），找出重複歌曲（交集）與無版權歌曲（差集），並處理歌名含括號、編號為 NULL 等髒資料問題。

資料結構簡述

表名	關鍵欄位
copyright_songs	id, name, singer, company, type
local_songs	id, name, singer, company

步驟 1：建立交集（歌名+歌手雙重匹配）

SELECT DISTINCT 
    t2.id AS copyright_id,
    t1.name, t1.singer,
    t1.id AS local_id,
    t1.company AS local_company,
    t2.company AS copyright_company,
    t2.type
INTO same_1
FROM copyright_songs t2
INNER JOIN local_songs t1 
    ON t1.name = t2.name AND t1.singer = t2.singer;

步驟 2：清洗歌名括號干擾（如「星語心願（節奏版）」）

-- 備份原表
SELECT * INTO local_songs_old FROM local_songs;

-- 移除中文括號及其後內容
UPDATE local_songs
SET name = LEFT(name, CHARINDEX('(', name) - 1)
WHERE CHARINDEX('(', name) > 0;

-- 重新比對交集
DROP TABLE same_1;
SELECT DISTINCT 
    t2.id, t1.name, t1.singer, t1.id AS local_id,
    t1.company, t2.company AS copyright_company, t2.type
INTO same_1
FROM copyright_songs t2
INNER JOIN local_songs t1 
    ON t1.name = t2.name AND t1.singer = t2.singer;

步驟 3：關聯回原始歌名（含括號版本）供人工覆核

SELECT DISTINCT 
    t1.id, t2.name AS original_name, t1.singer,
    t1.local_id, t1.local_company, t1.copyright_company, t1.type
FROM same_1 t1
INNER JOIN local_songs_old t2 ON t1.local_id = t2.id;

步驟 4：處理 NULL 編號髒資料

-- 檢查 NULL
SELECT * FROM local_songs WHERE id IS NULL;
SELECT * FROM same_1 WHERE local_id IS NULL;

-- 清除
DELETE FROM local_songs WHERE id IS NULL;
DELETE FROM same_1 WHERE local_id IS NULL;

步驟 5：取得差集（本地有、版權庫無）

SELECT * 
INTO no_local_songs
FROM local_songs
WHERE id NOT IN (SELECT local_id FROM same_1);

-- 檢視結果
SELECT * FROM no_local_songs;

關鍵技術點總結

操作	SQL 模式	適用場景	注意事項
交集	`INNER JOIN` / `INTERSECT` (2005+)	雙邊鍵值完全匹配	需處理資料清洗（大小寫、空白、標點）
差集 (A-B)	`NOT IN` / `NOT EXISTS` / `LEFT JOIN ... WHERE IS NULL` / `EXCEPT` (2005+)	找出僅存在於左表的行	`NOT IN` 遇 NULL 會回空集，優先用 `NOT EXISTS`
聯集	`UNION ALL` (保留重複) / `UNION` (去重)	合併同結構結果集	大量資料用 `UNION ALL` 避免排序