MySQL查询：如何从分组结果中筛选仅含指定子分类的用户数据

方案1（推荐，性能最优） #

直接按user_id分组，通过极值判断该用户所有记录的vegetarian取值均为1，仅需一次分组即可得到结果：

SELECT 
    user_id,
    1 AS vegetarian,
    COUNT(*) AS `count(*)`
FROM users
GROUP BY user_id
HAVING MIN(vegetarian) = 1 AND MAX(vegetarian) = 1;

逻辑说明：如果一个用户的vegetarian最小和最大值都是1，说明该用户不存在vegetarian=0的记录，完全符合筛选要求。

方案2（兼容原有分组逻辑） #

如果需要保留你原有按user_id+vegetarian分组的逻辑，可以搭配窗口函数实现筛选：

WITH user_group_stats AS (
    SELECT 
        user_id,
        vegetarian,
        COUNT(*) AS `count(*)`,
        COUNT(DISTINCT vegetarian) OVER(PARTITION BY user_id) AS veg_type_cnt
    FROM users
    GROUP BY user_id, vegetarian
)
SELECT user_id, vegetarian, `count(*)`
FROM user_group_stats
WHERE vegetarian = 1 AND veg_type_cnt = 1;

内容的提问来源于stack exchange，提问作者jeer