时间戳

首页 > 解决方案 > MySQL在加入后得到两条最近的记录

mysql - MySQL在加入后得到两条最近的记录

问题描述

我正在尝试为我列表中的每个帖子获取最近的两条评论。为此，我有一个如下所示的帖子表。

+----+-------------+------------+
| id |    post     |    date    |
+----+-------------+------------+
| 1  | First post  | 2020-01-01 |
| 2  | Second post | 2020-01-02 |
| 3  | Third post  | 2020-01-03 |
| 4  | Fourth post | 2020-01-04 |
+----+-------------+------------+

该帖子的另一张评论表如下

+----+---------------------+---------+------------+
| id |       comment       | post_id |    date    |
+----+---------------------+---------+------------+
| 1  |   Comment 1 post 1  |    1    | 2020-01-01 |
| 2  |   Comment 2 post 1  |    1    | 2020-01-02 |
| 3  |   Comment 1 post 2  |    2    | 2020-01-02 |
| 4  |   Comment 2 post 2  |    2    | 2020-01-03 |
| 5  |   Comment 3 post 2  |    2    | 2020-01-04 |
| 6  |   Comment 4 post 2  |    2    | 2020-01-05 |
| 7  |   Comment 1 post 3  |    3    | 2020-01-03 |
+----+---------------------+---------+------------+

我想根据这些表得到以下结果

+---------+---------------------+-----------+------------+------------------+--------------+
| post_id |       post          | post_date | comment_id |       comment    | comment_date |
+---------+---------------------+-----------+------------+------------------+--------------+
|      1  |    First post       | 2020-01-01|     1      | Comment 1 post 1 |  2020-01-01  |
|      1  |    First post       | 2020-01-01|     2      | Comment 2 post 1 |  2020-01-02  |
|      2  |    Second post      | 2020-01-02|     5      | Comment 3 post 2 |  2020-01-04  |
|      2  |    Second post      | 2020-01-02|     6      | Comment 4 post 2 |  2020-01-05  |
|      3  |    Third post       | 2020-01-03|     7      | Comment 1 post 3 |  2020-01-03  |
|      3  |    Third post       | 2020-01-03|    NULL    |       NULL       |     NULL     |
|      4  |    Fourth post      | 2020-01-04|    NULL    |       NULL       |     NULL     |
|      4  |    Fourth post      | 2020-01-04|    NULL    |       NULL       |     NULL     |
+---------+---------------------+-----------+------------+------------------+--------------+

我可以使用以下查询找到所有帖子以及最新评论。但是，找到这两条评论是我正在努力解决的问题。

SELECT p.id AS p_id, p.post,p.date AS post_date,
c.id AS comment_id, c.comment, c.date AS comment_date 
FROM posts AS p
LEFT JOIN comments AS c ON c.post_id = p.id AND c.id = (
    SELECT ci.id
    FROM comments AS ci
    WHERE ci.post_id = p.id
    ORDER BY ci.date DESC
    LIMIT 0,1 
)
ORDER BY p.date DESC;

我使用的 MySQL 版本是 5.7。我已经尝试过多次加入/联合，但我正在寻找更好的选择（如果有的话）。

任何帮助深表感谢。提前致谢。

标签： mysqldatabaseselectmysql-5.7

解决方案

在 MySQL 5.7 中，假设comments(id)可以用于对行进行排序，则可以在for 过滤的ON子句中使用相关子查询。LEFT JOIN

SELECT p.id AS p_id, p.post,p.date AS post_date,
    c.id AS comment_id, c.comment, c.date AS comment_date 
FROM posts AS p
LEFT JOIN comments AS c 
    ON c.post_id = p.id 
    AND (
        SELECT COUNT(*) 
        FROM comments c1 
        WHERE c1.post_id = c.post_id AND c1.id >= c.id
    ) <= 2

推荐阅读