MySQL连接和COUNT（*）从另一个表 [英] MySQL joins and COUNT(*) from another table

问题描述

我有两个表： groups 和 group_members 。

groups 表包含每个组的所有信息，例如其ID，标题，说明等。

The groups table contains all the information for each group, such as its ID, title, description, etc.

在 group_members 表中，它列出了每个组之外的所有成员，如下所示：

In the group_members table, it lists all the members who are apart of each group like this:

group_id | user_id
1 | 100
2 | 23
2 | 100
9 | 601

基本上，我想在一个页面上列出三个组，我只想列出组有超过四个成员。在<？php while？> 循环中，我想要四个成员，而不是该组。我没有麻烦列出组，并列出成员在另一个内部循环，我只是不能细化组，以便只有那些有超过4个成员显示。

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.

有人知道如何做到这一点吗？我确定它是与MySQL连接。

Does anybody know how to do this? I'm sure it's with MySQL joins.

推荐答案

MySQL使用 HAVING 语句。

MySQL use HAVING statement for this tasks.

您的查询将如下所示：

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

此外，请确保在数据库模式中为JOINS中使用的密钥设置索引它可能会影响您的网站效果。

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.

这篇关于MySQL连接和COUNT（*）从另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！