分组查询后如何基于子集行数据计算Sum与Count？

需要编写SQL查询语句，根据条件从DemoTable表中选取分组后的行集合，并在SELECT子句中对子集数据进行求和统计。

演示表结构及数据 #

DemoTable表的结构和数据如下：

原查询问题 #

尝试对StaticKey分组后，在SELECT子句中计算分组内GroupKey='A'的总和（Sum）与记录数（Count），编写的查询语句如下：

select 
    DT.GroupKey,       
    (select sum(D.Value) from DemoTable D where D.ID in (DT.ID) and D.GroupKey = 'A') as 'Sum of A''s',
    (select COUNT(D.ID) from DemoTable D where D.ID in (DT.ID) and D.GroupKey = 'A')  as 'Count of A''s'
from DemoTable DT
group by DT.StaticKey;

预期结果应为Sum=4、Count=2，但实际得到Sum=2、Count=1——原因是子查询仅获取到单个ID，而非分组后的所有ID集合。

目前使用GROUP_CONCAT(DT.ID)能得到逗号分隔的ID字符串，但想知道是否能直接获取可用于子查询的ID集合。

附建表及测试SQL #

CREATE TABLE DemoTable
(
    ID        INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    GroupKey  varchar(200)     null     default null,
    StaticKey varchar(200)     not null default 'A',
    Value     varchar(200)     null     default null,
    PRIMARY KEY (ID)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

insert into DemoTable (GroupKey, Value) values ('A', 2);
insert into DemoTable (GroupKey, Value) values ('A', 2);
insert into DemoTable (GroupKey, Value) values ('B', 2);
insert into DemoTable (GroupKey, Value) values ('B', 2);
insert into DemoTable (GroupKey, Value) values ('C', 2);
insert into DemoTable (GroupKey, Value) values ('C', 2);

-- 测试查询
select DT.GroupKey,
       (select sum(D.Value) from DemoTable D where D.ID in (DT.ID) and D.GroupKey = 'A') as 'Sum of A''s',
       (select COUNT(D.ID) from DemoTable D where D.ID in (DT.ID) and D.GroupKey = 'A')  as 'Count of A''s'
from DemoTable DT
group by DT.StaticKey;

DROP TABLE DemoTable;

解决方案 #

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

无需子查询，直接在主查询中用SUM(CASE...)和COUNT(CASE...)实现，效率更高：

select 
    StaticKey,
    SUM(CASE WHEN GroupKey = 'A' THEN Value ELSE 0 END) as 'Sum of A''s',
    COUNT(CASE WHEN GroupKey = 'A' THEN ID ELSE NULL END) as 'Count of A''s'
from DemoTable
group by StaticKey;

该语句按StaticKey分组，直接计算每组内GroupKey='A'的Value总和与记录数，可得到预期的Sum=4、Count=2。

方法2：子查询关联分组键 #

如果一定要用子查询，可通过分组键（如StaticKey）关联，而非ID集合：

select 
    DT.StaticKey,
    (select sum(D.Value) from DemoTable D where D.StaticKey = DT.StaticKey and D.GroupKey = 'A') as 'Sum of A''s',
    (select COUNT(D.ID) from DemoTable D where D.StaticKey = DT.StaticKey and D.GroupKey = 'A') as 'Count of A''s'
from DemoTable DT
group by DT.StaticKey;

此方式通过StaticKey关联子查询，直接获取分组内所有符合条件的记录。

关于“获取可用于子查询的ID集合” #

MySQL无法直接返回类似数组的ID集合供IN使用，但可通过两种间接方式实现：

• 用GROUP_CONCAT拼接ID字符串后，通过FIND_IN_SET函数判断：

select 
    DT.StaticKey,
    GROUP_CONCAT(DT.ID) as GroupedIDs,
    (select sum(D.Value) from DemoTable D where FIND_IN_SET(D.ID, GROUP_CONCAT(DT.ID)) and D.GroupKey = 'A') as 'Sum of A''s'
from DemoTable DT
group by DT.StaticKey;

不过该方法性能较差，不适合大数据量场景。

• 更合理的方式是通过分组键关联（如方法2），逻辑清晰且性能更优。

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