转换重复查找查询所有下降，但带有最新的记录

Question

我有一个重复的发现者查询...

SELECT 
    Author, Name, TrackNum, title_id, COUNT(*) 
FROM 
    db.table 
GROUP BY 
    Author, Name, TrackNum, title_id 
HAVING 
    COUNT(*) > 1 

它返回那些重复的记录，以及有多少计数。

我想扩大这个范围，这样它就会删除每个副本的最新版本。我想我们可以使用id或datetime字段，我可以在表中找到哪些记录。还是有另一种方式？

编辑：我走了一半......

SELECT 
    * 
FROM 
    db.table 
GROUP BY 
    Author, Name, TrackNum, title_id 
HAVING 
    COUNT(*) > 1 
ORDER BY 
    Name, TrackNum 

以上似乎表明每个副本的一个副本。当我在工作台中删除它们时，我只剩下剩余的最新唯一记录。我只想进一步自动化这一步。此外，这不包括两个以上相同的记录。

Answer 1

提出的解决方案

# 
# Step 01) Create Temp Key Tables 
# 
CREATE TABLE KeysToKeep 
(
    id INT NOT NULL, 
    PRIMARY KEY (id) 
); 
CREATE TABLE KeysToDrop LIKE KeysToKeep; 
# 
# Step 02) Collect All id values you want to Keep 
# 
INSERT INTO KeysToKeep 
    SELECT id FROM 
    (SELECT MAX(id) id,Author, Name, TrackNum, title_id 
    FROM db.table GROUP BY Author, Name, TrackNum, title_id) A 
; 
# 
# Step 03) Collect All id values you want to Drop 
# 
INSERT INTO KeysToDrop 
SELECT A.id FROM db.table A LEFT JOIN KeysToKeep USING (id) WHERE B.id IS NULL; 
# 
# Step 04) Do the Mass Delete 
# 
DELETE A.* FROM db.table A INNER JOIN KeysToDrop B USING (id); 
# 
# Step 05) Remove Temp Key Tables 
# 
DROP TABLE KeysToKeep; 
DROP TABLE KeysToDrop; 

提出的解决方案（短版）

# 
# Step 01) Create Temp Key Table 
# 
CREATE TABLE KeysToKeep 
(
    id INT NOT NULL, 
    PRIMARY KEY (id) 
); 
# 
# Step 02) Collect All id values you want to Keep 
# 
INSERT INTO KeysToKeep 
    SELECT id FROM 
    (SELECT MAX(id) id,Author, Name, TrackNum, title_id 
    FROM db.table GROUP BY Author, Name, TrackNum, title_id) A 
; 
# 
# Step 03) Do the Mass Delete 
# 
DELETE A.* FROM db.table A LEFT JOIN KeysToKeep B USING (id) WHERE B.id IS NULL; 
# 
# Step 04) Remove Temp Key Table 
# 
DROP TABLE KeysToKeep; 

提出的解决方案（偏执版）

# 
# Step 01) Create Temp Key Table 
# 
CREATE TABLE KeysToKeep 
(
    id INT NOT NULL, 
    PRIMARY KEY (id) 
); 
# 
# Step 02) Collect All id values you want to Keep 
# 
INSERT INTO KeysToKeep 
    SELECT id FROM 
    (SELECT MAX(id) id,Author, Name, TrackNum, title_id 
    FROM db.table GROUP BY Author, Name, TrackNum, title_id) A 
; 
# 
# Step 03) Copy the Tables to Keep to Another Temp Table 
# 
CREATE TABLE db.table_new LIKE db.table; 
INSERT INTO db.table_new 
SELECT A.* FROM db.table A INNER JOIN KeysToKeep B USING (id); 
# 
# Step 04) Swap New and Old Tables 
# 
ALTER TABLE db.table RENAME db.table_old; 
ALTER TABLE db.table_new RENAME db.table; 
# 
# Step 05) Remove Temp Key Table 
# 
DROP TABLE KeysToKeep; 
# 
# Step 06) Drop the Old Table If the Content of db.table is Correct 
# 
DROP TABLE db.table_old; 

EPILOGUE

在前两种情况下s，请检查KeysToKeep和/或KeysToDrop以确保它们是保留或丢弃的关键。在最后一种情况下，如果您确定，请删除表格db.table_old。如果你不确定，你可以这样回滚：

ALTER TABLE db.table RENAME db.table_new; 
ALTER TABLE db.table_old RENAME db.table; 

给它一个尝试！