mysql - 如何从另一个计数结果中减去一个计数结果以满足mysql中的where子句？

Question

有没有办法在最后一个子查询(或其他地方，如果更合适的话)中包含 IF 语句来排除在 class_id 上计数的行 IF 有一个同 table 考勤栏对应0？

我想做的是，如果有 6 个人预订了一个类(class)(最大人数为 6)，但有 1 个人在类(class)开始前取消了 - 我希望结果为 5，从而显示该类(class)可用(为什么我依靠类(class) ID 而不是出勤字段)

SELECT c.*
FROM classes c
WHERE c.location_id = :location_id
AND (c.level_id = :current_level OR c.level_id = :previous_level OR c.level_id = :next_level)
AND c.datetime BETWEEN CURDATE() AND ADDDATE(CURDATE(),INTERVAL 14 DAY)
AND (c.class_id NOT IN (
     SELECT class_id 
     FROM swimmer_classes) 
     OR c.class_id IN (
                       SELECT class_id 
                       FROM swimmer_classes 
                       GROUP BY class_id 
                       HAVING COUNT(class_id) < 6))"

更新1

swimmer_classes架构

swimmer_class_id   swimmer_id    class_id   attendance
1                  1             1          null           
2                  2             1          null
3                  3             1          null
4                  4             1          null
5                  5             1          null
6                  6             1          0

结果:计数(class_id)= 6

所需输出

根据以下伪代码，所需计数 = 5

 Count(class_id) - Count(attendance of class_id) = 5 

Answer 1

这样的事情在您的场景中有效吗？

SELECT class_id 
FROM swimmer_classes
WHERE attendance <> 0 OR attendance is NULL
GROUP BY class_id 
HAVING COUNT(class_id) < 6

或者更啰嗦:

SELECT class_id FROM 
(
    SELECT class_id, count(*) as cnt
    FROM swimmer_classes
    WHERE attendance <> 0 OR attendance is NULL
    GROUP BY class_id 
) t
where t.cnt < 6