萬盛學電腦網

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

經典 SQL 面試題:刪除邏輯重複資料 (保留自動增量 ID 最小的一筆)

題目情境

二維表 T(F1~F7) 即「成績表」,欄位:學生ID、學生姓名、課程ID、課程名稱、成績、教師ID、教師姓名。
表中含有邏輯重複資料 (除自動增量欄位 F 外,其他欄位完全相同)。請刪除多餘的髒資料,保留 F 最小的一筆。

資料範例

學生ID學生姓名課程ID課程名稱成績教師ID教師姓名
S3王五K4政治53T4趙老師
S1張三K1數學61T1張老師
S2李四K3英語88T3李老師
S1張三K4政治77T4趙老師
S2李四K4政治67T5周老師
S3王五K2語文90T2王老師
S3王五K1數學55T1張老師
S1張三K2語文81T2王老師
S4趙六K2語文59T1王老師
S1張三K3英語37T3李老師
S2李四K1數學81T1張老師

標準解法:自聯結 + 不等式

DELETE L
FROM 成績表 L
JOIN 成績表 R
    ON L.學生ID = R.學生ID
   AND L.課程ID = R.課程ID
   AND L.F > R.F;

原理解析

  • 自聯結找出「學生ID + 課程ID」相同的列對 (L、R)。
  • 條件 L.F > R.F 確保只選出 F 較大 (較晚插入) 的那一列 (L)。
  • 每組重複資料中,F 最小的一筆不會被選中 (無 R.F 比它更小),故保留。
  • 無重複的資料不會產生符合 L.F > R.F 的配對,故不受影響。

替代寫法 (相關子查詢)

DELETE FROM 成績表
WHERE F > (
    SELECT MIN(F)
    FROM 成績表 T2
    WHERE T2.學生ID = 成績表.學生ID
      AND T2.課程ID = 成績表.課程ID
);

效能上自聯結通常優於相關子查詢,視資料量與索引而定。

預防重複的最佳實務

於建表時直接加上複合主鍵或唯一約束:

CONSTRAINT UQ_學生課程 UNIQUE (學生ID, 課程ID)
-- 或
PRIMARY KEY (學生ID, 課程ID)
但實務上原始資料常需先「洗滌」後才能安全套用約束,故清除髒資料技巧仍不可或缺。
copyright © 萬盛學電腦網 all rights reserved