更新时间:2023-01-19 14:58:12
$ p> select users.name,notes.subject,notes.heading,notes.body
from users,notes
其中users.id = notes.user_id
notes.timestamp =(从user_id = users.id的笔记中选择max(timestamp))
Let's say I have two tables, users and notes. Let's say the schemas look like this:
users
id, name, field2, field3
notes
id, user_id, subject, heading, body, timestamp
What I want to do is select every user, and the LATEST (just 1) note posted by each user based on the timestamp to show in an overview report.
How would I go about doing this? Please note that the DB is MySQL.
Thanks!
select users.name, notes.subject, notes.heading, notes.body
from users, notes
where users.id = notes.user_id
and notes.timestamp = (select max(timestamp) from notes where user_id = users.id)