Mysql Count with Inner join of两个表，Average Join
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16806837/
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 Count with Inner join of two tables, Average Join
提问by Michael Scott
Im eager to join two tables but can′t seem to get it right, the structure look like this.
我很想加入两个表，但似乎无法正确连接，结构看起来像这样。
Table1 (members)[UserLogin | Name ]
表1（成员）[用户登录| 姓名 ]
Table 2 (articles)[ArtID | Writer | Content] Article.writer is pointing to Members.UserLogin
表 2（文章）[ArtID | 作家 | 内容] Article.writer 指向 Members.UserLogin
I want to display with as little code as possible, the whole list of registered UserLogins but also a count of how many articles they might have written, or if they have′nt written any they should still be on the list as well.
我想用尽可能少的代码显示已注册的 UserLogins 的整个列表，还要显示他们可能写了多少文章，或者如果他们没有写任何文章，他们也应该仍然在列表中。
What I would like: [UserLogin | Name | ArticlesWritten]
我想要什么：[用户登录 | 姓名 | 文章撰写]
What I′ve got so far is:
到目前为止我得到的是：
SELECT UserLogin, Name, Writer, count(*)
FROM Article
INNER JOIN Members on Writer=UserLogin
GROUP BY UserLogin;
or
或者
SELECT UserLogin, Name, count(Writer)
FROM Article
LEFT JOIN Members ON UserLogin = Writer
GROUP BY 1;
Both of them lists everything fine in mysql, but none of them contains the UserLogins and Names of those who never has written any article. Can you guys point me in the right direction. I Understand the problem with my queries, but I have no idea on how to solve it.
它们都列出了 mysql 中的所有内容，但没有一个包含从未写过任何文章的用户登录名和姓名。你们能指出我正确的方向吗？我了解我的查询问题，但我不知道如何解决它。
I′ve found similar problems on this forum, but I have′nt got to any solution from them. Might be a language barriere or just a plain lack of basic mysql-knowledge.
我在这个论坛上发现了类似的问题，但我没有从他们那里得到任何解决方案。可能是语言障碍或只是缺乏基本的 mysql 知识。
回答by peterm
You can use subquery
您可以使用子查询
SELECT m.UserLogin,
m.Name,
(SELECT COUNT(*)
FROM Articles
WHERE Writer = m.UserLogin) ArticlesWritten
FROM Members m
Sample output:
示例输出：
| USERLOGIN | NAME | ARTICLESWRITTEN |
---------------------------------------
| user1 | Jhon | 2 |
| user2 | Helen | 0 |
Here is SQLFiddle
回答by John Woo
just use LEFT JOINan interchange the table names,
只需使用LEFT JOIN交换表名，
SELECT UserLogin, Name, count(Writer)
FROM Members
LEFT JOIN Article on Writer = UserLogin
GROUP BY UserLogin, Name
To further gain more knowledge about joins, kindly visit the link below:
要进一步了解有关联接的更多信息，请访问以下链接：
in your question you state, "foreign key is "Name" which is pointing to "Writer", isn't it writer is pointing to UserLoginin which table Articleis dependent on table Members?
在您的问题中，您指出，“外键是指向“Writer”的“名称”，是不是writer is pointing to UserLogin在哪个表Article中依赖于表Members？
回答by Deval Shah
SELECT
m.UserLogin,
m.Name,
(SELECT
COUNT(a.ArtID)
FROM Article a
WHERE a.Writer = m.UserLogin) AS ArticlesWritten
FROM Members m
回答by Ananth
Try this.
尝试这个。
SELECT m.UserLogin,m.Name,COUNT(a.ArtID) FROM Members m LEFT JOIN Article a ON a.Writer=m.Name GROUP BY m.Name;
