MySQL 多对多关系的单个 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2052333/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Single SQL query on many to many relationship
提问by yojimbo87
I have a simple database with few tables (and some sample columns):
我有一个包含几个表（和一些示例列）的简单数据库：
Posts(ID, Title, Content)
帖子（ID、标题、内容）
Categories(ID, Title)
类别（ID、标题）
PostCategories(ID, ID_Post, ID_Category)
PostCategories（ID，ID_Post，ID_Category）
Is there a way to create single SQL query which will return posts with categories that are assigned to each post?
有没有办法创建单个 SQL 查询，该查询将返回具有分配给每个帖子的类别的帖子？
回答by JMM
You can use the GROUP_CONCATfunction
您可以使用GROUP_CONCAT函数
select p.*, group_concat(DISTINCT c.title ORDER BY c.title DESC SEPARATOR ', ')
from Posts p
inner join PostCategories pc on p.ID = pc.ID_Post
inner join Categories c on pc.ID_Category = c.ID
group by p.id, p.title, p.content
回答by mitenka
Simple joins work well.
简单的连接效果很好。
SELECT posts.id, posts.title, categories.id, categories.title
FROM posts
JOIN posts_categories ON posts.id = posts_categories.post_id
JOIN categories ON posts_categories.category_id = categories.id
回答by RedFilter
select p.*, c.*
from Posts p
inner join PostCategories pc on p.ID = pc.ID_Post
inner join Categories c on pc.ID_Category = c.ID
If you mean with only one record per post, I will need to know what database platform you are using.
如果你的意思是每篇文章只有一条记录，我需要知道你使用的是什么数据库平台。
回答by John Hyland
Sure. If I understand your question correctly, it should be as simple as
当然。如果我正确理解你的问题，它应该很简单
SELECT Posts.title, Categories.title
FROM Posts, Categories, PostCategories
WHERE PostCategories.ID_Post = Posts.ID AND PostCategories.ID_Category = Categories.ID
ORDER BY Posts.title, Categories.title;
Getting one row per Post will be a little more complicated, and will depend on what RDBMS you're using.
每个 Post 获取一行会稍微复杂一些，这取决于您使用的 RDBMS。
回答by Shashi Dk
We can use this query also.
我们也可以使用这个查询。
select e.*,c.* from Posts e, Categories c, PostCategories cp where cp.id in ( select s.id from PostCategories s where s.empid=e.id and s.companyid=c.id );
