SQL内查询无法引用外层GROUP BY的brand_id问题求解

我有一张名为s2s_actions的表，数据如下：

id  brand_id  event_type
------  --------  ----------
    22       361           0
    29       361           1
    35         7           1
    40         7           0
    41        37           1
    42        37           0
    43        37           1

我需要统计每个brand_id对应的event_type=0和event_type=1的数量，尝试了以下SQL语句：

SELECT s2s_actions.brand_id, deposits.deposit_amount, signups.signup_amount 
FROM 
s2s_actions,
(SELECT COUNT(*) AS signup_amount FROM s2s_actions WHERE brand_id=s2s_actions.brand_id AND event_type=0) signups,
(SELECT COUNT(*) AS deposit_amount FROM s2s_actions WHERE brand_id=s2s_actions.brand_id AND event_type=1) deposits
GROUP BY brand_id

当在内查询中填入具体数字（如37）时可以正常运行，但无法引用外层GROUP BY的brand_id，请问该如何解决？

方法1：使用条件聚合（推荐） #

直接在主查询中用COUNT(CASE ...)来分别统计不同event_type的数量，不需要子查询，写法更简洁高效：

SELECT 
    brand_id,
    COUNT(CASE WHEN event_type = 0 THEN 1 END) AS signup_amount,
    COUNT(CASE WHEN event_type = 1 THEN 1 END) AS deposit_amount
FROM s2s_actions
GROUP BY brand_id;

• CASE WHEN会对每行数据做判断，符合条件时返回1，否则返回NULL
• COUNT()函数会忽略NULL值，因此刚好统计出对应event_type的行数

方法2：使用关联子查询（修正原写法） #

原写法的问题在于子查询没有正确关联外层表，需要给表起别名明确关联关系：

SELECT 
    main.brand_id,
    (SELECT COUNT(*) FROM s2s_actions sub WHERE sub.brand_id = main.brand_id AND sub.event_type = 0) AS signup_amount,
    (SELECT COUNT(*) FROM s2s_actions sub WHERE sub.brand_id = main.brand_id AND sub.event_type = 1) AS deposit_amount
FROM s2s_actions main
GROUP BY main.brand_id;

• 给外层表起别名main，子查询中用sub作为内层表别名，通过sub.brand_id = main.brand_id明确关联条件，就能正确引用外层的brand_id
• 也可以先通过DISTINCT获取所有唯一brand_id，避免分组：

SELECT 
    brand_id,
    (SELECT COUNT(*) FROM s2s_actions sub WHERE sub.brand_id = outer_brand.brand_id AND sub.event_type = 0) AS signup_amount,
    (SELECT COUNT(*) FROM s2s_actions sub WHERE sub.brand_id = outer_brand.brand_id AND sub.event_type = 1) AS deposit_amount
FROM (SELECT DISTINCT brand_id FROM s2s_actions) outer_brand;

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