如何在不将encid1加入GROUP BY的情况下保留该列用于表关联？

针对你遇到的00979. 00000 - "not a GROUP BY expression"错误，以下是两种符合业务需求的解决办法：

方法一：用聚合函数包裹encid1（推荐，适合同分组内encid1值唯一的场景） #

如果在f001, conid1, valid1, acc206这组分组条件下，encid1的取值是唯一的（或者业务上所有同分组的行encid1值一致），直接用MAX()或MIN()包裹encid1即可，既保留该列用于关联，又满足GROUP BY语法要求：

select
    f001,
    valid1,
    MAX(encid1) as encid1, -- 用聚合函数包裹，不影响业务值
    sum(val004) as AMOUNT#221,
    conid1,
    acc206
from
    (
        select
            f001,
            encid1,
            acc206,
            valid1,
            val004,
            conid1
        from
            table_tmp 
        where
                valid2 = 4 and acc206=0 -- for EUR 
    ) table1
group by
    f001,
    conid1,
    valid1,
    acc206

方法二：先聚合统计，再关联原表获取encid1（适合同分组内encid1有多个值的场景） #

如果同一分组下存在多个不同的encid1值，先单独完成聚合统计，再通过关联原表取出所有需要的encid1。如果需要避免重复行，可使用LISTAGG()将多个encid1拼接成字符串：

基础关联版（可能产生重复行） #

select
    agg.f001,
    agg.valid1,
    t.encid1,
    agg.AMOUNT#221,
    agg.conid1,
    agg.acc206
from
    -- 先做聚合统计
    (
        select
            f001,
            valid1,
            sum(val004) as AMOUNT#221,
            conid1,
            acc206
        from
            table_tmp 
        where
                valid2 = 4 and acc206=0 -- for EUR 
        group by
            f001,
            conid1,
            valid1,
            acc206
    ) agg
-- 关联原表获取encid1
inner join table_tmp t 
    on agg.f001 = t.f001 
    and agg.conid1 = t.conid1 
    and agg.valid1 = t.valid1 
    and agg.acc206 = t.acc206
    and t.valid2 = 4 and t.acc206=0

去重拼接版（避免重复行） #

select
    agg.f001,
    agg.valid1,
    LISTAGG(t.encid1, ',') WITHIN GROUP (ORDER BY t.encid1) as encid1_list, -- 拼接所有encid1
    agg.AMOUNT#221,
    agg.conid1,
    agg.acc206
from
    (
        select
            f001,
            valid1,
            sum(val004) as AMOUNT#221,
            conid1,
            acc206
        from
            table_tmp 
        where
                valid2 = 4 and acc206=0 -- for EUR 
        group by
            f001,
            conid1,
            valid1,
            acc206
    ) agg
inner join table_tmp t 
    on agg.f001 = t.f001 
    and agg.conid1 = t.conid1 
    and agg.valid1 = t.valid1 
    and agg.acc206 = t.acc206
    and t.valid2 = 4 and t.acc206=0
group by
    agg.f001,
    agg.valid1,
    agg.AMOUNT#221,
    agg.conid1,
    agg.acc206

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