Mysql Group by 子句与聚合结果上的 where 相关

Question

我有以下子订单表。

  mysql> select * from suborder;
+-------------+------------------+
| order_state | booking_group_id |
+-------------+------------------+
| CNF         |                1 |
| CNF         |                1 |
| CNF         |                2 |
| BLK         |                1 |
| CNF         |                2 |
+-------------+------------------+

我想获取所有 order_state 都是 CNF 的 booking_group_id。因此，对于上述数据，结果应该仅为值“2”，因为两行都有 order_state = CNF，其中 booking_data_id“1”，两行是 CNF，一行是 BLK，因此不会成为输出的一部分。

谢谢

Answer 1

您可以通过使用条件聚合的 HAVING 子句来进行分组:

select booking_group_id 
from suborder
group by booking_group_id
having count(case when order_state <> 'CNF' then 1 end) = 0

或者您也可以使用NOT EXITS:

select distinct * 
from suborder AS s1
where order_state = 'CNF' and 
      not exists (select 1 
                  from suborder AS s2
                  where s2.booking_group_id = s1.booking_group_id and
                        s2.order_state <> 'CNF')