有没有一种办法,包括在最后一个子查询的IF语句(或其他地方是否更适合),以排除行正在对数类标识码 如果有相应的0在同一个表的列出席?
我想做的是,如果有6个人预订了一个班级(最大人数为6人),但在班级之前取消了1个人-我希望结果为5,从而显示该班级有空(为什么Im指望班级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
结果:Count(class_id)= 6
要求的输出
根据以下伪代码,所需计数= 5
Count(class_id) - Count(attendance of class_id) = 5
这样的事情在您的情况下可行吗?
SELECT class_id
FROM swimmer_classes
WHERE attendance <> 0 OR attendance is NULL
GROUP BY class_id
HAVING COUNT(class_id) < 6
或者,更long的:
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
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句