我有一個簡單的數據庫方案，由於之前設置不當，它有許多重複記錄。

記錄表

|--------------------------------------------------------|
| id  | subid  | catid  | start_date | end_date          |
|--------------------------------------------------------|
| 1   |  1     | 1      | 2015-01-01 | 2015-12-31        |
| 2   |  1     | 1      | 2015-01-01 | 2015-12-31        |
| 3   |  2     | 1      | 2015-01-01 | 2015-12-31        |
| 4   |  2     | 1      | 2015-01-01 | 2015-12-31        |
| 5   |  3     | 1      | 2015-01-01 | 2015-12-31        |
|--------------------------------------------------------|

我想刪除記錄 2 和 4，因為該 subid/catid 組合已經有一條記錄涵蓋相同的日期。

我希望能夠做到：

DELETE from record_table WHERE catid='1' AND 
   start_date<='2015-02-13' AND 
   end_date>='2015-02-13' AND 
   [the user has two records for the previous conditions]

解決方案

• id為每個subid,catid組合創建一個具有最小值的 TempTable(record_table_keep)
• record_table在和record_table_keep使用之間執行 DELETE JOINLEFT JOIN
• 只刪除record_tableid不在的記錄record_table_keep

提議的查詢

CREATE TABLE record_table_keep
SELECT id,subid,catid FROM record_table WHERE 0=1;
ALTER TABLE record_table_keep ADD PRIMARY KEY (id);
INSERT INTO record_table_keep (id,subid,catid)
SELECT MIN(id) id,subid,catid FROM record_table GROUP BY subid,catid;
DELETE A.* FROM record_table A
LEFT JOIN record_table_keep B USING (id)
WHERE B.id IS NULL;
DROP TABLE record_table_keep;

試一試 ！！！

DELETE FROM record_table a
WHERE EXISTS( SELECT subid
                FROM record_table b
               WHERE a.subid= b.subid
                 AND a.id < b.id )

引用自：https://dba.stackexchange.com/questions/91881