mysql count其他表,MySQL连接和COUNT（*）从另一个表

I have two tables: groups and group_members.

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

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

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.

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

解决方案

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

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.