having count的使用场景之一

order表

order_id	order_no	create_time	create_cd
1	A1
2	A2

user表

user_id	user_name	order_id	user_age
1	aa	1	12
2	bb	1	12
3	cc	1	12

需求是查出user中必须有aa，且（有bb或者有cc）的订单，即可以存在aa-bb-其他,aa-cc-其他

可以用

SELECT
    order.order_id,
    GROUP_CONCAT(user.user_name)
FROM
    order
LEFT JOIN user ON order.order_id = user.order_id
GROUP BY
    order.order_id
HAVING
    COUNT(CASE WHEN user.user_name IN ('aa', 'bb') THEN 1 END) > 0
    AND COUNT(CASE WHEN user.user_name = 'cc' THEN 1 END) > 0;