如何修改SQL语句实现按project_ref分组汇总msr_cnt字段？

原SQL语句 #

select      m.project_ref,
            (   select count(*)
                from (values (m.[EWI/IWI]), (m.Glazing), (m.Solar), (m.CWI), (m.Boiler), (m.TRV), (m.LI), (m.RIRI), (m.UFI), (m.ASHP)) as v(col)
                where v.col <> ''
            ) as 'msr_cnt'
from        SMSDB1.dbo.ops_measure m
where       'msr_cnt' <> '0'
--group by  m.project_ref

问题说明 #

原SQL会对ops_measure表中同一project_ref的每条记录单独计算msr_cnt（示例中每行值为2），最终输出多行相同project_ref的结果。需求是将同一project_ref的所有msr_cnt汇总求和（示例中得到总和14），返回单条聚合记录。

修改后的SQL语句 #

select 
    project_ref,
    SUM(msr_cnt) as total_msr_cnt
from (
    select 
        m.project_ref,
        (   select count(*)
            from (values (m.[EWI/IWI]), (m.Glazing), (m.Solar), (m.CWI), (m.Boiler), (m.TRV), (m.LI), (m.RIRI), (m.UFI), (m.ASHP)) as v(col)
            where v.col <> ''
        ) as msr_cnt
    from SMSDB1.dbo.ops_measure m
    where (
        select count(*)
        from (values (m.[EWI/IWI]), (m.Glazing), (m.Solar), (m.CWI), (m.Boiler), (m.TRV), (m.LI), (m.RIRI), (m.UFI), (m.ASHP)) as v(col)
        where v.col <> ''
    ) <> 0
) as sub
group by project_ref

修改要点 #

• 将原查询嵌套为子查询，先计算每条记录的msr_cnt值，再在外层按project_ref分组并执行求和操作
• 修正原WHERE子句的逻辑错误：原语句中'msr_cnt' <> '0'是对字符串字面量进行比较，无法过滤实际的msr_cnt数值，改为在子查询中直接计算并过滤掉msr_cnt为0的记录
• 去掉原注释的group by语句，在外层查询中对project_ref分组，实现同一项目的聚合汇总

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