时间戳

首页 > 解决方案 > MySql：如何获取每个组的最新行？

mysql - MySql：如何获取每个组的最新行？

问题描述

我需要为每个符号获取最新的行 ORDERED BY created_at LIMIT 1。所以如果我想要 AAPL 和 GOOGL 最新的行，我会返回第 5 行和第 10 行，因为它们created_at是最新的。我知道这将类似于
SELECT * FROM stocks WHERE symbol IN ("AAPL","GOOGL") ORDER BY created_at
我该怎么做？

stocks
+----+--------+---------------+---------------------------+
| id | symbol | price         |  created_at               |
+----+--------+---------------+---------------------------+
|  1 | AAPL   |        498.20 |2020-08-25 17:40:48.692584 |
|  2 | AAPL   |        498.21 |2020-08-25 17:42:48.743049 |
|  3 | AAPL   |        498.22 |2020-08-25 17:43:48.783471 |
|  4 | AAPL   |        498.23 |2020-08-25 17:44:48.844545 |
|  5 | AAPL   |        498.24 |2020-08-25 17:45:48.868454 |
|  6 | GOOGL  |        100.20 |2020-08-25 17:50:48.901309 |
|  7 | GOOGL  |        100.21 |2020-08-25 17:52:48.935137 |
|  8 | GOOGL  |        100.22 |2020-08-25 17:54:48.957983 |
|  9 | GOOGL  |        100.23 |2020-08-25 17:56:48.973070 |
| 10 | GOOGL  |        100.24 |2020-08-25 17:58:49.002871 |
+----+--------+---------------+---------------------------+



Results:
+----+--------+---------------+---------------------------+
| id | symbol | price         |  created_at               |
+----+--------+---------------+---------------------------+
|  5 | AAPL   |        498.24 |2020-08-25 17:45:48.868454 |
| 10 | GOOGL  |        100.24 |2020-08-25 17:58:49.002871 |
+----+--------+---------------+---------------------------+

标签： mysqlsql

解决方案

SELECT t1.*
FROM stocks t1
NATURAL JOIN ( SELECT symbol, MAX(created_at) created_at
               FROM stocks
               GROUP BY symbol ) subquery
-- WHERE symbol IN ("AAPL","GOOGL") 
-- ORDER BY created_at

推荐阅读