mysql - 使用 group by/order by 执行 MSQL 查询

Question

我正在处理个人收件箱。这是我的收件箱表:

收件箱

email -- createdDate -- message

mail1@test.com -- 12/02/2017 -- message1
mail1@test.com -- 13/02/2017 -- message2
mail3@test.com -- 14/02/2017 -- message3
mail2@test.com -- 10/02/2017 -- message4
mail2@test.com -- 05/02/2017 -- message5
mail3@test.com -- 11/02/2017 -- message6
mail3@test.com -- 16/02/2017 -- message7

查询后我想要什么:

email -- createdDate -- message
mail3@test.com -- 16/02/2017 -- message7
mail1@test.com -- 13/02/2017 -- message2
mail2@test.com -- 10/02/2017 -- message4

我已经尝试过使用 group by 和 order by 以及 left join 进行一些操作，但执行起来需要很长时间。

我当前的查询是:

SELECT a.email, a.createdDate, a.message FROM inbox a LEFT JOIN inbox b ON (a.email = b.email AND a.createdDate < b.createdDate)WHERE b.createdDate IS NULL ORDER BY a.createdDate DESC LIMIT 0,20

我也在我的电子邮件和createdDate 字段上创建了索引。你能帮我通过执行 mysql 查询得到这个结果吗？非常感谢..

Answer 1

首先对子查询进行排序，然后只需分组依据所选列:

select * from (select email, STR_TO_DATE(createdDate, '%d/%m/%Y') as myDate, message from inbox order by myDate desc) as t
 group by email

如果同一日期可能有多个电子邮件，您的表格中应该有一个 id 列(我假设是这样)，然后您需要添加 id DESC 按顺序:

select * from (select email, STR_TO_DATE(createdDate, '%d/%m/%Y') as myDate, message from inbox order by myDate desc, id desc) as t
 group by email