我需要编写一个查询来使用 mysql 表从两列中检索值
我的桌子有以下结构
| ID | to_user_id | from_user_id | message | datetime |
| 1 | 21 | 07 | hi | 2012-05-10 04:13:01 |
| 2 | 07 | 21 | hello | 2012-05-10 04:17:51 |
| 3 | 21 | 11 | what's up | 2012-05-10 04:21:51 |
| 4 | 21 | 02 | hi dude | 2012-05-10 04:43:51 |
我想编写一个查询以仅从所有from_user_id获取最新消息
我会尝试
$query="select * from messages WHERE datetime = (SELECT MAX(datetime) FROM messages)";
但它显示错误
"where 子句"中的未知列"日期时间"
select t1.from_user_id, t1.message
from t1
join (
select from_user_id, max([datetime]) as [datetime]
from t1
group by from_user_id
) t2 on t1.from_user_id = t2.from_user_id
and t1.[datetime] = t2.[datetime]
您也可以尝试:
select a.from_user_id, a.message
from table1 a
WHERE a.datetime = (SELECT MAX(datetime) FROM table1 x WHERE x.from_user_id = a.from_user_id)