请考虑下表:
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)
我想从条形图表中获取最近更新的foo_id和状态,并且foo_id也包含多个状态
我试过以下查询:
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;
在不同的机器上出现以下错误:
未处理的拒绝SequelizeDatabaseError:SELECT列表的表达式#2不在GROUP BY子句中,并且包含非聚合列'dev.d1.status',它在功能上不依赖于GROUP BY子句中的列;这与sql_mode = only_full_group_by不兼容
如何从条形图表中获取每个foo_id的最新状态?
1 回答
如果你只想要max updated_at的id,你可以使用
或使用连接