一、分组取最大(or新)的一条数据
方案一 from a,b 子查询
步骤分解:
第一步,按account_id分组,并找出最大的更新时间max(update_time) AS max_update_time
第二步,按account_id关联两张表
SELECT
a.*
FROM
table_name a,
(
SELECT
account_id,
max(update_time) AS max_update_time
FROM
table_name
GROUP BY
account_id
) b
WHERE
a.account_id = b.account_id;
方案二 join子查询
SELECT
account_id,
new_value,
update_time
FROM
table_name a
JOIN (
SELECT
account_id,
max(update_time) AS max_update_time
FROM
table_name
GROUP BY
account_id
) b ON a.account_id = b.account_id
AND a.update_time = b.max_update_time;
二、编写一个 SQL 删除语句来 删除 所有重复的电子邮件,只保留一个id最小的唯一电子邮件。
DELETE p1.* from Person p1,Person p2 where p1.id>p2.id and p1.email=p2.email;