MySQL GROUP BY ...在同一字段中具有不同的值 [英] MySQL GROUP BY...HAVING different values same field

问题描述

我想使用与以下类似的查询来检索events中的所有行，这些行至少具有与'male'和'female'相对应的event_attendances行.下面的查询不返回任何行(肯定有一些events的性别都来自event_attendances).

I want to use a query similar to the following to retrieve all rows in events that have at least one corresponding event_attendances row for 'male' and 'female'. The below query returns no rows (where there certainly are some events that have event_attendances from both genders).

有没有子查询的方法(由于在我的应用程序中生成SQL的方式，子查询对我来说很难实现)?

Is there a way to do this without a subquery (due to the way the SQL is being generated in my application, a subquery would be considerably more difficult for me to implement)?

SELECT * FROM events e
LEFT JOIN event_attendances ea ON (e.id = ea.event_id)
GROUP BY e.id
HAVING ea.gender = 'female' AND ea.gender = 'male'

推荐答案

使用

HAVING sum(ea.gender = 'female') > 0 
   AND sum(ea.gender = 'male') > 0

或

HAVING count(distinct ea.gender) = 2

顺便说一句，当您进行分组时，应该使用子查询来获取所有数据.

BTW you should use a subquery to get all data when you group.

SELECT * 
FROM events
where id in
(
    SELECT events.id 
    FROM events
    LEFT JOIN event_attendances ON (events.id = event_attendances.event_id)
    GROUP BY events.id
    HAVING count(distinct event_attendances.gender) = 2
)

这篇关于MySQL GROUP BY ...在同一字段中具有不同的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！