CASE WHEN查询结果不符，新增Active状态计数条件求助

让我帮你分析下问题根源，以及如何修改代码达到你的需求：

问题出在哪？ #

你把status字段加入了GROUP BY子句，这导致同一个食堂（比如Canteen01）会因为status的不同（Active/Not Active）被拆分成两个独立分组。所以查询结果里出现了重复的Canteen01行，而且total的计算也被拆分，没有统计该食堂的所有计数器总数，这完全不符合你想要按食堂整体判断配额的需求。

修改后的SQL代码 #

我们需要先按食堂分组，单独统计每个食堂的Active状态计数器数量和总计数器数量，再用Active状态的计数来判断配额：

SELECT 
    CASE WHEN active_count < 2 THEN 'Available' ELSE 'Full' END AS quota,
    id_canteen,
    name_canteen,
    total
FROM (
    SELECT 
        a.id_canteen,
        a.name_canteen,
        COUNT(b.id_counter) AS total, -- 统计该食堂所有计数器的总数
        COUNT(CASE WHEN b.status = 'Active' THEN b.id_counter END) AS active_count -- 仅统计Active状态的计数器数量
    FROM canteen_A a 
    JOIN canteen_B b ON a.id_canteen = b.id_canteen 
    GROUP BY a.id_canteen, a.name_canteen -- 仅按食堂分组，不再带status
) AS canteen_stats;

代码解释 #

1. 子查询canteen_stats：负责按食堂维度聚合数据，计算两个核心值：
   • total：该食堂下所有计数器的总数
   • active_count：该食堂中状态为Active的计数器数量（用条件聚合实现）
2. 外层查询：用active_count < 2的条件判断quota，完全符合你的新增需求
3. 移除GROUP BY中的status：确保每个食堂只返回一行结果，避免分组拆分导致的重复行

运行后得到的预期结果 #

内容的提问来源于stack exchange，提问作者Muhammad Alief Agusta