如何在使用案例中删除mysql中的重复项 [英] how to delete duplicates in mysql using case

问题描述

现在我正在使用类似的方法删除mysql表中的重复项:

Right now I am using something like this to delete duplicates in mysql table :

delete t2 from my_table1 as t1, my_table1 as t2 where
t1.TestCase = t2.TestCase and t2.id > t1.id;

说我有一个像这样的结构:

say I have a structure like this :

ID  TestCAse Result  
1   T1       PASS
2   T2       FAIL
3   T3       FAIL
4   T3       PASS

现在，在上述情况下，T3是重复项，并且如果我使用上面提到的SQL，它将删除结果为PASS的第四行，但这是我要保留的行我希望删除第3行，即FAIL.

now, in the above case T3 is duplicate entry, and if I use the SQL that I mentioned above, it would delete 4th row where the result is PASS, but this is the row that I want to keep and I want row 3 to get deleted which is FAIL.

请帮忙吗?

谢谢.

推荐答案

如果在重复的情况下我理解正确，则要删除失败"而不是通过"?在这种情况下，您可以进行以下查询:

if I undersstand correctly in case of duplicate you want to delete the "FAIL" and not the "PASS" ? in this case you can have the following query:

delete t2 from my_table1 as t1, my_table1 as t2 where
t1.TestCase = t2.TestCase and t2.id != t1.id and t2.Result='FAIL'; 

但是当所有重复项的列结果中都包含"FAIL"时，您要怎么办?通过上面的查询，两个都将被删除.在这种情况下，您要保留一个吗?

but what do you want to do when all the duplicate have "FAIL" in their column result? With the query above, both will be removed. Do you want to keep one in this case ?

这篇关于如何在使用案例中删除mysql中的重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！