修改删除重复项的 MySQL 查询 [英] Modifying a MySQL Query that Removes Duplicates

问题描述

我有一个查询如下:

SELECT * FROM 
    (SELECT * FROM persons ORDER BY date DESC) AS p
GROUP BY first_name,last_name,work_phone 

如果您还没有弄清楚，这会删除具有重复姓名和工作电话号码的条目，只留下最近的条目.您应该了解 person 表中的另一个字段，一个名为 DELETED 的二进制字段.

If you hadn't figured it out already, this removes entries with duplicate names and work phone numbers, leaving only the most recent. There is another field in the person table you should know about, a binary field called DELETED.

问题是，如果有这种性质的重复，我不希望一行被考虑，如果它的 DELETED 值是 TRUE，不管它的日期值有多近.但是，如果一行没有重复项，则无论它有什么 DELETED 值，它都应该包含在结果中.

The problem is, if there is a duplicate of this nature, I don't want a row to be considered if its DELETED value is TRUE regardless of how recent its date value is. However, if a row has no duplicates it should be included in the results no matter what DELETED value it has.

如果存在重复项，则永远不会出现所有重复项都具有 DELETED = TRUE 的情况，至少不会删除一个.

If duplicates exist there is never a case where all duplicates have DELETED = TRUE, at least one will not be deleted.

推荐答案

SELECT * FROM   
  (SELECT * FROM persons ORDER BY deleted ASC, date DESC) AS p
GROUP BY first_name,last_name,work_phone 

这篇关于修改删除重复项的 MySQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！