mysql - 从重复行中选择最大值
问题描述
表结构:
表中有可以通过代码字段复制的记录。此外,这些记录有一个列deleted_date,它决定了被删除记录的时间。我创建一个查询:
SELECT id
FROM analyzes
WHERE code IN (
SELECT code
FROM analyzes
WHERE deleted = 1
GROUP BY code
HAVING count(code)>1
)
但它不能正常工作。请提示一下,在谁那里有很多sql经验,是否可能通过sql来发出给定的请求?
解决方案
您可以subquery
使用not exists
:
select a.*
from analyzes a
where not exists (select 1 from analyzes a1 where a1.code = a.code and a1.deleted = 0) and
deleted_date = (select max(a1.deleted_date) from analyzes a1 where a1.code = a.code);