最好的mysql查询选择多个行，每个行需要来自另一个表的多个关联行 [英] Best mysql query for selecting multiple rows that EACH need multiple associative rows from another table

问题描述

我很难准确地说出这个问题，所以我给你一个例子。

I'm having a hard time coming up with exactly how to phrase this question so I'll give you an example.

让我们说一个用户请求一个页面这将是显示多个文章和多个评论的那些文章中的每一个。因此，您不仅需要选择多个文章记录，而且每个记录都需要有多个与之相关的注释记录。

Let's say a user requests a page that is going to show multiple articles AND multiple comments for EACH one of those articles. So not only do you need to select multiple records of articles but each one of those records is going to need to have several comment records associated with it.

最多最佳查询来完成这个？感谢。

What is the most optimal query to accomplish this? Thanks.

推荐答案

这不是绝对的对或错，加入返回更大的数据集，但可以在一个查询中获得所有内容

This is no absolute right or wrong, join will return larger set of data but can get everything in one query

根据您的需求，如果您只需要注释计数+注释ID列表（供以后使用）考虑使用group_concat

Depend on your requirement, if you just need the comment count + list of comments ID (for later usage), consider to use group_concat

select 
  article.*, 
  group_concat(comment.id) as comments, 
  count(comment.id) as comments_count
from 
  article
left join comment 
on comment.article_id=article_id
group by article.id;

列注释将包含所有注释id，comments_count返回每篇文章的注释

the columns comments will contains all the comments id, and the comments_count return number of comments for each article

PS ：我认为左连接更合适

PS : I think left join is more appropriate

这篇关于最好的mysql查询选择多个行，每个行需要来自另一个表的多个关联行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！