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.

您的查询将如下所示:

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屋！