mysql 取一行_mysql – 查询只从具有相同值的多行中获取一行

要获取MySQL中的最新行,您需要使用连接或相关子查询：

SELECT id, user_receiver, user_sender, post_id, action, date, is_read

FROM notification n

WHERE user_receiver=$ses_user and

date = (select max(date)

from notification n2

where n2.user_sender = n.user_sender and

n2.action = n.action and

n2.post_id = n.post_id and

n2.is_read = n.is_read

)

order by date desc;

在其他数据库中,您只需使用row_number()函数(或Postgres中的distinct).

编辑：

对于最大的id：

SELECT id, user_receiver, user_sender, post_id, action, date, is_read

FROM notification n

WHERE user_receiver=$ses_user and

id = (select max(id)

from notification n2

where n2.user_sender = n.user_sender and

n2.action = n.action and

n2.post_id = n.post_id

)

order by date desc;

如果你想要isread = 1的行数,那么你可以这样做：

SELECT sum(is_read = 1)

FROM notification n

WHERE user_receiver=$ses_user and

id = (select max(id)

from notification n2

where n2.user_sender = n.user_sender and

n2.action = n.action and

n2.post_id = n.post_id

);