mysql - 如果日期相同，则从具有最大日期和最大 ID 的表中选择 ID

Question

举个例子，我有一个 MySQL 表:

id  category  date        value
1   a         2013-01-02  7
2   a         2013-01-01  2
3   a         2013-01-01  3
4   b         2013-01-01  1
5   b         2013-01-02  4
6   b         2013-01-03  5
7   c         2013-01-03  4
8   c         2013-01-03  8

我需要为每个类别选择带 MAX(date) 的记录，但如果日期相同，则带 MAX(id)。因此，预期结果必须是:

id  category  date        value
1   a         2013-01-02  7
6   b         2013-01-03  5
8   c         2013-01-03  8

如您所见，同一类别的日期无法排序(如 id=4,5)。

我试过这样的查询:

SELECT * FROM mytable t1
INNER JOIN 
(
   SELECT category, MAX(date) AS MAXDATE
   FROM mytable
   GROUP BY category
) t2
ON t1.category = t2.category
AND t1.date = t2.MAXDATE

当我们在表中没有相同的日期时，它工作正常。但就我而言，它将返回 ID 最低的记录:

   id  category  date        value
    1   a         2013-01-01  7
    6   b         2013-01-03  5
    7   c         2013-01-03  4

Answer 1

您走在正确的轨道上，但您需要更改您的加入。我想你想要的是以下内容:

SELECT * FROM mytable t1
INNER JOIN 
(
   SELECT MAX(id) AS ID, MAX(date) AS MAXDATE
   FROM mytable
   GROUP BY category
) t2
ON t1.id = t2.ID
AND t1.date = t2.MAXDATE

如前所述，前一个不适用于每个用例。以下可能。

SELECT a.*
FROM mytable AS a,
(
    SELECT category, date, MAX(id) AS ID
    FROM mytable
    GROUP BY category, date
) AS b,
(
    SELECT category, MAX(date) AS date
    FROM mytable
    GROUP BY category
) AS c
WHERE b.category = c.category
AND b.date = c.date
AND a.id = b.id