如果您有两个“产品”列相同的不同记录,则可以使用某些条件选择不需要的记录,例如:
CREATE TABLE victims AS
SELECT MAX(entryDate) AS date, Product, COUNT(*) AS dups FROM ProductsTable WHERE ...
GROUP BY Product HAVING dups > 1;
然后,您可以在ProductTable和Victims之间执行DELETE JOIN。
或者,您可以仅选择产品,然后针对其他JOIN条件执行DELETE操作,例如具有无效的CustomerId,EntryDate NULL或任何其他情况。如果您“知道”只有一个有效副本的产品,并且所有其他副本都可通过无效数据进行识别,则此方法适用。
假设您有相同的记录(或您既有相同的记录又有非相同的记录,或某些产品存在多个副本而您不知道哪个是哪个)。您需要运行完全相同的查询。然后,在ProductsTable上运行SELECT查询,并选择DISTINCT匹配要去重的产品代码的所有产品,按Product进行分组,并为所有字段选择合适的聚合函数(如果相同,则任何聚合都可以使用。否则,我通常尝试MAX或MIN)。这将“保存”每个产品的一行。
然后,在执行DELETE JOIN并删除所有重复产品之后,只需重新导入保存和去重的子集到主表中即可。
当然,在DELETE JOIN和INSERT SELECT之间,数据库将处于不稳定状态,其中至少有一个重复产品已经消失。
另一种在MySQL中应该有效的方法:
CREATE TABLE deduped AS SELECT * FROM ProductsTable WHERE false;
CREATE UNIQUE INDEX deduped_ndx ON deduped(Product);
INSERT IGNORE INTO deduped SELECT * FROM ProductsTable;
ALTER TABLE ProductsTable RENAME TO ProductsBackup;
ALTER TABLE deduped RENAME TO ProductsTable;
注意:如果你想区分“好记录”和“无效重复记录”,上述方法不起作用。它只在存在冗余重复记录或者你不关心保留哪一行而舍弃哪一行时才有效!
编辑:
你说“重复记录”有无效字段,这种情况下你可以使用排序技巧修改上述方法:
SELECT * FROM ProductsTable ORDER BY Product, FieldWhichShouldNotBeNULL IS NULL;
如果您的产品只有一行,那么选择它就好了。如果有多个产品,则首先选择那个(FieldWhichShouldNeverBeNull IS NULL)为FALSE的产品(即实际上不为空的产品),并插入。由于IGNORE子句静默地反弹到产品的唯一性上,所有其他产品都会被忽略。这不是一个很好的解决方法(请检查我是否在子句中混淆了true和false!),但它应该可以工作。
编辑:
这是一个简单的表格,用于说明问题。
CREATE TABLE ProductTable ( Product varchar(10), Description varchar(10) );
INSERT INTO ProductTable VALUES ( 'CBPD10', 'C-Beam Prj' );
INSERT INTO ProductTable VALUES ( 'CBPD11', 'C Proj Mk2' );
INSERT INTO ProductTable VALUES ( 'CBPD12', 'C Proj Mk3' );
目前还没有索引,也没有主键。我们仍然可以将产品(Product)声明为主键。
但是会有一些问题。当两条新记录都具有NULL描述时,情况就不妙了。
然而,第二条记录是有效的产品,因为我们之前不知道CBPD14,因此我们不想完全丢失这条记录。但是我们确实想要删除虚假的CBPD10。
INSERT INTO ProductTable VALUES ( 'CBPD10', NULL );
INSERT INTO ProductTable VALUES ( 'CBPD14', NULL );
一个粗鲁的 DELETE FROM ProductTable WHERE Description IS NULL 并不可行,它会删除非重复项 CBPD14。
因此我们采用以下方式。首先获取重复项列表:
SELECT Product, COUNT(*) AS Dups FROM ProductTable GROUP BY Product HAVING Dups > 1;
我们假设:“每一组坏记录中至少有一条好记录”。
我们通过提出相反的假设并查询来检查这个假设。如果一切正常,我们希望此查询不返回任何结果。
SELECT Dups.Product FROM ProductTable
RIGHT JOIN ( SELECT Product, COUNT(*) AS Dups FROM ProductTable GROUP BY Product HAVING Dups > 1 ) AS Dups
ON (ProductTable.Product = Dups.Product
AND ProductTable.Description IS NOT NULL)
WHERE ProductTable.Description IS NULL;
为了进一步验证,我插入了两条记录来表示这种故障模式;现在我确实希望上面的查询返回新代码。
INSERT INTO ProductTable VALUES ( "AC5", NULL ), ( "AC5", NULL );
现在,“check”查询确实返回,
AC5
因此,Dups的生成看起来不错。
我现在开始删除所有无效的重复记录。如果有重复但是有效的记录,它们将保持重复,除非找到某些条件来区分它们中的一个“好”记录并将所有其他记录声明为“无效”(可能使用与“描述”不同的字段再次重复此过程)。
但是,这里有个问题。目前,您不能从表中删除并选择子查询中的相同表(http://dev.mysql.com/doc/refman/5.0/en/delete.html)。因此,需要进行一些小的解决方法:
CREATE TEMPORARY TABLE Dups AS
SELECT Product, COUNT(*) AS Duplicates
FROM ProductTable GROUP BY Product HAVING Duplicates > 1;
DELETE ProductTable FROM ProductTable JOIN Dups USING (Product)
WHERE Description IS NULL;
现在,只要无效记录出现在Dups表中,它们就将被全部删除。因此,我们的CBPD14记录将不受影响,因为它没有在其中出现。CBPD10的“好”记录将保持原样,因为其描述不为NULL并非真实情况。其余所有记录都会消失。
请注意,如果一条记录
没有有效记录,
但是却是重复的,则该记录的
所有副本都将被删除 -
没有一个能幸存下来。
为了避免这种情况,您可以首先将表示此失败模式的行使用上述查询进行选择(检查应返回空),然后将它们插入到删除后的主表中(可能需要使用事务)。