MySQL连接和COUNT（*）从另一个表

编程入门行业动态更新时间:2024-10-22 18:39:08

本文介绍了MySQL连接和COUNT（*）从另一个表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！问题描述

我有两个表： 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中使用的密钥设置索引它可能会影响您的网站效果。