mysql 返回出连续日期的sql_mysql – SQL查询返回最近日期和更多的重复项

仅返回其date_modified字段中具有最新值的条目,忽略其余条目：

SELECT p.* FROM

( SELECT max(date_modified) as most_recent_date

FROM person

GROUP BY first_name,last_name,work_phone ) p1

JOIN person p

ON p.date_modified = p1.most_recent_date

假设date_modified对于我们分组的每个字段组合都是唯一的,这将很有效.如果不是,我们必须加入一个唯一的字段,取一个满足最新条件的任意UUID(限制1).

SELECT p.* FROM

( SELECT *,max(date_modified) as most_recent_date

FROM person

GROUP BY first_name,last_name,work_phone ) p1

JOIN person p

ON p.UUID =

( SELECT p_uniq.UUID

FROM person p_uniq

WHERE p_uniq.first_name = p1.first_name

AND p_uniq.last_name = p1.last_name

AND p_uniq.work_phone = p1.work_phone

AND p_uniq.date_modified = p1.most_recent_date

LIMIT 1 )

最后,返回没有“重复”的每个条目的完整行：

SELECT * , count( * ) AS entries

FROM `person`

GROUP BY first_name, last_name, work_phone

HAVING entries =1