来自唯一mysql索引的最新日期时间 [英] Latest datetime from unique mysql index

问题描述

我有一张桌子.它的ID为pk，索引为[服务，检查，日期时间].

I have a table. It has a pk of id and an index of [service, check, datetime].

id  service  check   datetime  score
---|-------|-------|----------|-----
1  | 1     |  4    |4/03/2009 | 399
2  | 2     |  4    |4/03/2009 | 522
3  | 1     |  5    |4/03/2009 | 244
4  | 2     |  5    |4/03/2009 | 555
5  | 1     |  4    |4/04/2009 | 111
6  | 2     |  4    |4/04/2009 | 322
7  | 1     |  5    |4/05/2009 | 455
8  | 2     |  5    |4/05/2009 | 675

提供服务2我需要为每个具有最大日期的唯一检查选择行.所以我的结果看起来像这张表.

Given a service 2 I need to select the rows for each unique check where it has the max date. So my result would look like this table.

id  service  check   datetime  score
---|-------|-------|----------|-----
6  | 2     |  4    |4/04/2009 | 322
8  | 2     |  5    |4/05/2009 | 675

对此有简短查询吗?我最好的就是这个，但是它返回了太多的支票.我只需要在最新日期时间进行唯一检查即可.

Is there a short query for this? The best I have is this, but it returns too many checks. I just need the unique checks at it's latest datetime.

SELECT * FROM table where service=?;

推荐答案

首先，您需要找出每张支票的最大日期

First you need find out the biggest date for each check

 SELECT `check`, MAX(`datetime`)
 FROM YourTable
 WHERE `service` = 2
 GROUP BY `check`

然后重新连接以获取其余数据.

Then join back to get the rest of the data.

 SELECT Y.*
 FROM YourTable Y
 JOIN ( SELECT `check`, MAX(`datetime`) as m_date
        FROM YourTable
        WHERE `service` = 2
        GROUP BY check) as `filter`
  ON Y.`service` = `filter`.service
 AND Y.`datetime` = `fiter`.m_date
 WHERE Y.`service` = 2

这篇关于来自唯一mysql索引的最新日期时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！