MySQL 从另一个表连接和 COUNT(*)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4847089/
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 joins and COUNT(*) from another table
提问by hohner
I have two tables: groupsand group_members.
我有两个表：groups和group_members。
The groupstable contains all the information for each group, such as its ID, title, description, etc.
该groups表包含每个组的所有信息，例如其 ID、标题、描述等。
In the group_memberstable, it lists all the members who are apart of each group like this:
在group_members表中，它列出了每个组中的所有成员，如下所示：
group_id | user_id
1 | 100
2 | 23
2 | 100
9 | 601
Basically, I want to list THREE groups on a page, and I only want to list groups which have MORE than four members. Inside the <?php while ?>loop, I then want to four members who are apart of that group. I'm having no trouble listing the groups, and listing the members in another internal loop, I just cannot refine the groups so that ONLY those with more than 4 members show.
基本上，我想在一个页面上列出三个组，我只想列出成员超过四个的组。在<?php while ?>循环内部，我想要四个属于该组的成员。我在列出组并在另一个内部循环中列出成员时没有问题，我只是无法细化组，以便仅显示超过 4 个成员的组。
Does anybody know how to do this? I'm sure it's with MySQL joins.
有人知道怎么做这个吗？我确定它与 MySQL 连接有关。
回答by Nazariy
MySQL use HAVINGstatement for this tasks.
MySQL 使用HAVING语句执行此任务。
Your query would look like this:
您的查询将如下所示：
SELECT g.group_id, COUNT(m.member_id) AS members
FROM groups AS g
LEFT JOIN group_members AS m USING(group_id)
GROUP BY g.group_id
HAVING members > 4
example when references have different names
引用具有不同名称时的示例
SELECT g.id, COUNT(m.member_id) AS members
FROM groups AS g
LEFT JOIN group_members AS m ON g.id = m.group_id
GROUP BY g.id
HAVING members > 4
Also, make sure that you set indexes inside your database schema for keys you are using in JOINS as it can affect your site performance.
此外，请确保在数据库架构中为您在 JOINS 中使用的键设置索引，因为它会影响您的站点性能。
回答by Reena Shirale
SELECT DISTINCT groups.id,
(SELECT COUNT(*) FROM group_members
WHERE member_id = groups.id) AS memberCount
FROM groups
回答by bbb
Your groups_maintable has a key column named id. I believe you can only use the USINGsyntax for the join if the groups_fanstable has a key column with the same name, which it probably does not. So instead, try this:
您的groups_main表有一个名为 的键列id。我相信您只能使用连接USING语法，如果groups_fans表有一个同名的键列，它可能没有。所以相反，试试这个：
LEFT JOIN groups_fans AS m ON m.group_id = g.id
LEFT JOIN groups_fans AS m ON m.group_id = g.id
Or replace group_idwith whatever the appropriate column name is in the groups_fanstable.
或者替换group_id为表中任何适当的列名groups_fans。
回答by crazeyez
Maybe I am off the mark here and not understanding the OP but why are you joining tables?
也许我在这里不合时宜，不理解 OP 但你为什么要加入表格？
If you have a table with members and this table has a column named "group_id", you can just run a query on the members table to get a count of the members grouped by the group_id.
如果您有一个包含成员的表，并且该表有一个名为“group_id”的列，则您只需在成员表上运行查询即可获取按 group_id 分组的成员计数。
SELECT group_id, COUNT(*) as membercount
FROM members
GROUP BY group_id
HAVING membercount > 4
This should have the least overhead simply because you are avoiding a join but should still give you what you wanted.
这应该有最少的开销，因为您正在避免加入，但仍然应该给您想要的东西。
If you want the group details and description etc, then add a join from the members table back to the groups table to retrieve the name would give you the quickest result.
如果您想要组详细信息和描述等，则从成员表中添加一个连接到组表以检索名称会给您最快的结果。
