gpt4 book ai didi

php - MYSQL JOIN所有子行,父行只显示一次

转载 作者:行者123 更新时间:2023-12-01 00:25:50 25 4
gpt4 key购买 nike

我有一个简单的文章和评论系统,包含以下表格:

文章表:

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 Fiddle with Demo .

结果是:

|     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 Fiddle with Demo .结果是:

|     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 |

关于php - MYSQL JOIN所有子行,父行只显示一次,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/14530396/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com