多内连接场景下使用SUM函数的SQL报错问题求解

咱们先拆解下你遇到的问题，再一步步修正你的SQL：

问题根源分析 #

你收到的Expression not in GROUP BY key 'field1'错误，核心原因是：

• 子查询里的SELECT引用了未聚合的原始字段field1，但GROUP BY只指定了column_id——严格SQL模式要求，非聚合字段必须出现在GROUP BY中，否则数据库不知道该取该字段的哪个值对应分组结果。
• 另外你的子查询还存在两个隐藏问题：没有返回关联需要的column_id，也没返回你后续要用到的yyyy_mm_dd，导致外层查询无法正常关联和过滤；如果要获取每日求和数据，仅按column_id分组是不够的，必须加上日期维度。

修正后的SQL语句 #

-- 修正DROP TABLE语法，原写法不符合SQL标准
DROP TABLE IF EXISTS your_result_table;
CREATE TABLE your_result_table AS SELECT
    fbp.*,
    bcs.yyyy_mm_dd,
    bcs.sum_field1, -- 使用聚合后的求和字段
    cspd.*
FROM xxx.fbp fbp
INNER JOIN table2 st ON (fbp.id = st.xml_id)
INNER JOIN (
    -- 重构聚合子查询：返回酒店ID、日期、每日求和值
    SELECT 
        bcs.column_id,
        bcs.yyyy_mm_dd,
        -- COALESCE替代CASE，更简洁地处理SUM的NULL结果
        COALESCE(SUM(bcs.field1), 0) AS sum_field1
    FROM table2 bcs
    WHERE bcs.yyyy_mm_dd >= '2019-04-21'
    -- 按酒店ID+日期分组，实现每日求和
    GROUP BY bcs.column_id, bcs.yyyy_mm_dd
) AS bcs ON bcs.column_id = fbp.id
-- 将日期关联条件移到JOIN子句，避免不必要的笛卡尔积
INNER JOIN table3 cspd ON (fbp.id = cspd.id AND cspd.yyyy_mm_dd = bcs.yyyy_mm_dd)
WHERE 
    st.id IN (9)
    AND st.date IS NULL;

关键调整说明 #

1. 修复聚合逻辑：
   用COALESCE(SUM(bcs.field1), 0)替代你原来的CASE语句——SUM本身会忽略NULL值，只有当分组内所有field1都是NULL时才返回NULL，用COALESCE可以直接把这种情况转为0，逻辑更简洁。
2. 完善分组维度：
   GROUP BY同时包含column_id和yyyy_mm_dd，这样就能得到每个酒店每日的field1求和结果，符合你的需求。
3. 修正关联与过滤：
   子查询明确返回column_id和yyyy_mm_dd，保证外层可以正常关联；把cspd的日期条件移到JOIN子句，确保关联的是同一日期的元数据，避免数据冗余。
4. 规范语法：
   修正了DROP TABLE的错误写法，标准语法是DROP TABLE IF EXISTS 表名。

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