如何从表中获取每个id的最新状态?

Consider the following table:

考虑下面的表格:

mysql> desc foo;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255) | NO   |     | NULL    |                |
| created_at | datetime     | NO   |     | NULL    |                |
| updated_at | datetime     | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> desc bar;
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| id              | int(11)      | NO   | PRI | NULL    | auto_increment |
| foo_id          | int(11)      | NO   | MUL | NULL    |                |
| status          | varchar(255) | YES  |     | NULL    |                |
| created_at      | datetime     | NO   |     | NULL    |                |
| updated_at      | datetime     | NO   |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

I want to fetch the recently updated foo_id and status from the bar table and the foo_id consist of multiple status as well

我想从bar表获取最近更新的foo_id和状态，而foo_id也包含多个状态

I tried following query:

我试着以下查询:

SELECT foo_id, status
FROM bar d1
WHERE d1.updated_at =
    (SELECT MAX(d2.updated_at)
     FROM bar d2
     WHERE d1.foo_id = d2.foo_id)
GROUP BY foo_id
ORDER BY updated_at DESC;

Got the following error on different machine:

在不同的机器上得到以下错误:

Unhandled rejection SequelizeDatabaseError: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'dev.d1.status' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

未处理的拒绝SequelizeDatabaseError: SELECT list的表达式#2不在GROUP BY子句中，并且包含非聚合列'dev.d1。不依赖于GROUP BY子句中的列的状态;这与sql_mode=only_full_group_by不兼容

How do I fetch the latest status of each foo_id from bar table?

如何从bar表获取每个foo_id的最新状态?

1 个解决方案

#1

If you just want the id of the max updated_at you could use

如果您只想要max updated_at的id，可以使用它

select foo_id, status 
from bar 
where updated_at = (
select MAX(updated_at) 
from bar 
)

or using a join

或者使用一个连接

select foo_id, status 
from bar 
INNER JOIN (
select MAX(updated_at)  max_date
from bar 
) t on t.max_date  = bar.updated_at

1 个解决方案

#1

更多相关文章

随机推荐