MYSQL 连接所有子行并仅显示一次父行 - MYSQL JOIN all child rows and display parent row once only

MYSQL JOIN all child rows and display parent row once only

本文关键字：一次显示连接 MYSQL | 更新日期: 2023-09-27

我有一个简单的文章和评论系统，其中包含下表：

文章表：

id | writer | text
 1 | Bob    | first article
 2 | Marley | second article

注释表：

id | article_id | comment
 1 |      1     |  i love this article
 2 |      1     |  good one
 3 |      2     |  waiting for more

我想选择每篇文章，并在它下面有评论。我使用以下查询：

SELECT * FROM articles LEFT JOIN comments ON articles.id = comments.article_id

我得到的结果：

 articles.id   | articles.writer | articles.text  | comments.id | comments.article_id | comments.comment
 1             | Bob             | first article  |    1        |         1           | i love this article
 1             | Bob             | first article  |    2        |         1           | good one  
 2             | Marley          | second article |    3        |         2           | waiting for more

我想要什么：

articles.id   | articles.writer | articles.text  | comments.id | comments.article_id | comments.comment
 1            | Bob             | first article  |    1        |         1           | i love this article
NULL          | NULL            |    NULL        |    2        |         1           | good one  
 2            | Marley          | second article |    3        |         2           | waiting for more

那么我如何选择每篇文章及其评论并仅显示文章一次而不是每条评论

谢谢

您可以在MySQL中使用用户变量来执行此操作：

SELECT 
  case when rownum =1 then id else null end id,
  case when rownum =1 then writer else null end writer,
  case when rownum =1 then text else null end text,
  comment
FROM
(
  SELECT a.id, a.writer, a.text,
    c.article_id, c.comment, 
    @rownum := case
                when @prev = a.id
                  and @prev_art = c.article_id
                then @rownum+1 else 1 end rownum,
    @prev := a.id p_id,
    @prev_art := c.article_id p_art
  FROM articles a
  LEFT JOIN comments c
    ON a.id = c.article_id 
  ORDER BY a.id, article_id
) src

请参阅 SQL 摆弄演示。

结果是：

|     ID | WRITER |           TEXT |             COMMENT |
----------------------------------------------------------
|      1 |    Bob |  first article | i love this article |
| (null) | (null) |         (null) |            good one |
|      2 | Marley | second article |    waiting for more |

编辑，如果您需要comment.id，则可以将其添加到结果中：

SELECT 
  case when rownum =1 then id else null end id,
  case when rownum =1 then writer else null end writer,
  case when rownum =1 then text else null end text,
  commentid,
  article_id,
  comment
FROM
(
  SELECT a.id, a.writer, a.text,
    c.article_id, c.comment, c.id commentid,
    @rownum := case
                when @prev = a.id
                  and @prev_art = c.article_id
                then @rownum+1 else 1 end rownum,
    @prev := a.id p_id,
    @prev_art := c.article_id p_art
  FROM articles a
  LEFT JOIN comments c
    ON a.id = c.article_id 
  ORDER BY a.id, article_id
) src

请参阅 SQL 摆弄演示。结果是：

|     ID | WRITER |           TEXT | COMMENTID | ARTICLE_ID |             COMMENT |
-----------------------------------------------------------------------------------
|      1 |    Bob |  first article |         1 |          1 | i love this article |
| (null) | (null) |         (null) |         2 |          1 |            good one |
|      2 | Marley | second article |         3 |          2 |    waiting for more |