mysql - 如何选择2天在mysql表中的最新值行

我发现很难把我的头缠在这上面。例如，如果我有一个像这样的表：

table: cat
  -------------------------------------------------------------
  | id       | currency   |bank         |created_at           |
  -------------------------------------------------------------
  | 1        | a          |1            |2015-05-12 19:30:55  |
  | 2        | a          |1            |2015-05-12  20:30:55 |
  | 3        | b          |2            |2015-05-12  19:30:55 |
  | 4        | s          |1            |2015-05-13 19:30:55  |
  | 5        | b          |2            |2015-05-13  23:30:55 |
  | 6        | b          |2            |2015-05-13  19:30:55 |
  ----------------------------------------------------

我的预期结果：

 -------------------------------------------------------------
  | id       | currency   |bank         |created_at           |
  -------------------------------------------------------------
  | 2        | a          |1            |2015-05-12  20:30:55 |
  | 3        | b          |2            |2015-05-12  19:30:55 |
  | 4        | s          |1            |2015-05-13 19:30:55  |
  | 5        | b          |2            |2015-05-13  23:30:55 |
  ----------------------------------------------------

我尝试过的

SELECT p.id,p.currencyname, p.bank, date(p.created_at) as d, max(time(created_at)) as t, p.currencyname from cat as p  GROUP by p.currencyname, d

最佳答案

看起来您想要每个日历日每种货币的最新（最大时间值）行。尊重您在专业使用SQL时，花时间仔细思考需求并准确描述需求至关重要。

这是您获得所需结果的方式。

首先，您要查找每个日历日（created_at）中每种货币的最新（MAX()）行的DATE()值。执行此操作的查询如下所示（http://sqlfiddle.com/#!9/a0e097/1/0）：

            SELECT DATE(created_at) day, currency, MAX(created_at) created_at
              FROM cat
             GROUP BY DATE(created_at), currency

然后，您可以将其用作子查询，并将其加入表中。该联接将从表中提取详细记录。您订购它使它有意义（http://sqlfiddle.com/#!9/a0e097/2/0）。

 SELECT cat.id, cat.currency, cat.bank, cat.created_at
   FROM cat
   JOIN (
                 SELECT DATE(created_at) day, currency, MAX(created_at) created_at
                  FROM cat
                 GROUP BY DATE(created_at), currency
        ) a ON cat.currency = a.currency AND cat.created_at = a.created_at
  ORDER BY cat.id