php - 如果任何记录处于活动状态或不活动状态，MySQL 单一查询以获取记录

Question

我有一个场景，我有 2 个如下表

groups

id| name
1 | Group 1
2 | Group 2
3 | Group 3


users

id| name | group_id | sub_status
1 | John | 1        | Active
2 | Doe  | 1        | Inactive
3 | Simon| 2        | Active
4 | David| 3        | Active

现在我需要选择订阅状态为活动或非活动的组。就像我正在寻找Active组一样，任何单曲我都应该得到 Group1、Group2 和 Group3，因为 Group 1 中有一个活动订阅。如果我查询Inactive组，我应该得到null因为 Group1 有一个活动订阅

标签： phpmysqlsqldatabasejoin

Answer 1

您可以按每个组计算活动和非活动记录。之后，您可以按该计数过滤组。例如，您可以使用以下查询找到活动组

SELECT 
 groups.id,
 groups.name,
 sum(IF(users.sub_status = 'Active', 1, 0)) as active_count 
 sum(IF(users.sub_status = 'Inactive', 1, 0)) as inactive_count  
FROM groups
LEFT JOIN users ON users.group_id = group.id
GROUP BY groups.id, groups.name
HAVING active_count > 0

和非活动组：

SELECT 
 groups.id,
 groups.name,
 sum(IF(users.sub_status = 'Active', 1, 0)) as active_count 
 sum(IF(users.sub_status = 'Inactive', 1, 0)) as inactive_count  
FROM groups
LEFT JOIN users ON users.group_id = group.id
GROUP BY groups.id, groups.name
HAVING active_count = 0