mysql - MySQL SELECT 从 2 个表中获取最新记录

Question

我的查询有问题。我的数据库表：

第一张桌子

Notification
id |  Subject  | date_add
---+ ----------+---------
 1 | Subject1 | 2020-04-01 12:06:00
 2 |  Subject2 | 2020-04-18 19:12:59
 3 |  Subject3 | 2020-04-21 13:46:01
 4 |  Subject4 | 2020-04-20 13:46:01 

第二张桌子

Notification_post
    id | user_id | notification_id | description | date_add as post_date
    ---+---------+--------+-------------+---------
     1 | 1        | 1      | Text 1      | 2020-04-19 12:06:00
     2 | 2        | 1      | Text 2      | 2020-04-20 19:12:59
     3 | 3        | 1      | Text 3      | 2020-04-21 19:44:36
     4 | 2        | 2      | Text 1      | 2020-04-21 19:48:24
     5 | 1        | 2      | Text 2      | 2020-04-21 19:55:00

例如像帖子和评论。我想在帖子中发表最新评论。

预期输出：

Notification_ID |User_id | Subject | description  | post_date
----------------+--------+---------+--------------+-------------
       1        |    3   |  Subject1 |    text 3  | 2020-04-21 19:44:36
       2        |    1   | Subject2  |   Text 2   |  2020-04-21 19:55:00

我的查询

SELECT * 
FROM notification n 
LEFT JOIN notification_post p ON p.notification_id=n.id 
GROUP BY p.notification_id 
ORDER BY p.date_add DESC

输出：

 Notification_ID |User_id | Subject | description  | post_date
 ----------------+--------+---------+--------------+-------------
       1             1     Subject1     Text 1        2020-04-19 12:06:00
       2             2     Subcject2    Text 1        2020-04-21 19:48:24

我尝试使用 MAX(date_add) 但不起作用或者我做错了什么

标签： mysqlsql

Answer 1

您可以在statements中使用带有MAX()聚合date_add列的子查询：GROUPBY notification_idJOIN

SELECT p1.notification_id, p1.user_id, n.Subject, p1.description, p2.post_date
  FROM notification_post p1
  JOIN notification n 
    ON p1.notification_id = n.id
  JOIN ( SELECT notification_id, MAX(date_add) as post_date
           FROM notification_post  
          GROUP BY notification_id ) p2
    ON p2.notification_id = p1.notification_id  
   AND p2.post_date = p1.date_add

Demo