MySQL 多左连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1990352/
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
MySQL Multiple Left Joins
提问by John M.
I am trying to create a news page for a website I am working on. I decided that I want to use correct MySQL queries (meaning COUNT(id) and joins instead of more than one query or num_rows.) I'm using a PDO wrapper, that should function fine, and this still fails when run directly through the MySQL CLI application.
我正在尝试为我正在处理的网站创建一个新闻页面。我决定要使用正确的 MySQL 查询（意思是 COUNT(id) 和 joins 而不是多个查询或 num_rows。）我使用的是 PDO 包装器，它应该可以正常工作，但直接通过MySQL CLI 应用程序。
Basically, I have 3 tables. One holds the news, one holds the comments and one holds the users. My aim here is to create a page which displays all (will paginate later) the news posts titles, bodies, authors and dates. This worked fine when I used a second query to get the username, but then I decided I'd rather use a JOIN.
基本上，我有3张桌子。一个保存新闻，一个保存评论，一个保存用户。我的目标是创建一个页面，显示所有（稍后将分页）新闻帖子的标题、正文、作者和日期。当我使用第二个查询来获取用户名时，这很好用，但后来我决定我宁愿使用 JOIN。
So what's the problem? Well, I need two joins. One is to get the author's username and the other to get the number of comments. When I simply go for the author's username, all works as expected. All the rows (there are 2) in the news table are displayed. However, when I added this second LEFT JOIN for the comments row, I end up only receiving one row from news (remember, there are 2,) and COUNT(comments.id) gives me 2 (it should display 1, as I have a comment for each post.)
所以有什么问题？嗯，我需要两个连接。一个是获取作者的用户名，另一个是获取评论数。当我只是去寻找作者的用户名时，一切都按预期工作。显示新闻表中的所有行（有 2 行）。但是，当我为评论行添加第二个 LEFT JOIN 时，我最终只收到了来自新闻的一行（记住，有 2 个），而 COUNT(comments.id) 给了我 2（它应该显示 1，因为我有每个帖子的评论。）
What am I doing wrong? Why is it only displaying one news post, and saying that it has two comments, when there are two news posts, each with one comment?
我究竟做错了什么？为什么只显示一条新闻，并说有两条评论，当有两条新闻，每条一条评论？
SELECT news.id, users.username, news.title, news.date, news.body, COUNT(comments.id)
FROM news
LEFT JOIN users
ON news.user_id = users.id
LEFT JOIN comments
ON comments.news_id = news.id
Also, just to be sure about one other thing, my left join to comments is the correct way to get all posts regardless of whether they have comments or not, correct? Or would that be a right join? Oh, one last thing... if I switch comments.news_id = news.id to news.id = comments.news_id, I get 0 results.
另外，为了确定另一件事，我的左加入评论是获取所有帖子的正确方法，无论他们是否有评论，对吗？或者那会是一个正确的加入？哦，最后一件事...如果我将comments.news_id = news.id 切换到news.id = comments.news_id，我会得到0 个结果。
回答by Mark Byers
You're missing a GROUP BY clause:
您缺少 GROUP BY 子句：
SELECT news.id, users.username, news.title, news.date, news.body, COUNT(comments.id)
FROM news
LEFT JOIN users
ON news.user_id = users.id
LEFT JOIN comments
ON comments.news_id = news.id
GROUP BY news.id
The left join is correct. If you used an INNER or RIGHT JOIN then you wouldn't get news items that didn't have comments.
左连接是正确的。如果您使用 INNER 或 RIGHT JOIN，那么您将不会获得没有评论的新闻项目。
回答by rizon
To display the all details for each news post title ie. "news.id" which is the primary key, you need to use GROUP BY clause for "news.id"
显示每个新闻帖子标题的所有详细信息，即。“news.id”是主键，你需要对“news.id”使用GROUP BY子句
SELECT news.id, users.username, news.title, news.date,
news.body, COUNT(comments.id)
FROM news
LEFT JOIN users
ON news.user_id = users.id
LEFT JOIN comments
ON comments.news_id = news.id
GROUP BY news.id
