SQL按所有关键字段分组仍出现重复及ORA-01452错误求解

问题根因 #

你遇到的重复行和0值问题主要由以下3个逻辑漏洞导致：

• 维度替换逻辑位置错误：你先按原始可能为NULL的node字段分组，再在上层查询将NULL值统一替换为Failed to record。即使Oracle分组时会将NULL归为同一组，但如果存在关联逻辑漏洞导致同维度生成多条聚合结果为0的分组，替换后就会出现主键重复。
• 左关联生成无效行：你对scm.internal表使用左关联，当关联条件不满足时，it.trans_type等字段全为NULL，此时sum的条件（trans_type='T1'/'T2'）永远不成立，最终received和sent的聚合结果均为0，且node字段为NULL，替换后全部归为Failed to record的维度行。
• 过滤条件逻辑漏项：你最外层的过滤条件为received > 0 or sent < 0，但如果存在received和sent均为0的行，本应被过滤，但如果因为关联逻辑错误导致部分行的sent聚合结果为0但被误放，就会产生多余行。

修复方案 #

第一步：调整SQL逻辑，修复语法及逻辑漏洞 #

修正后的查询如下：

select id1, id2, id3, node, period, received, sent
from (
  select 
    id1, id2, id3, 
    -- 将node空值替换逻辑移到分组前，统一维度值
    nvl(node, 'Failed to record') as node,
    period,
    sum(case when trans_type = 'T1' and number_packs > 0 then number_packs else 0 end) as received,
    -- 发件数如果需要存储正数可以加abs()，根据业务需求调整
    sum(case when trans_type = 'T2' and number_packs < 0 then number_packs else 0 end) as sent
  from (
    select
      tr.id1, tr.id2, tr.id3, tr.period, tr.number_packs,
      it.trans_type,
      case 
        when it.trans_type = 'T1' and tr.number_packs > 0 then kt.node 
        when it.trans_type = 'T2' and tr.number_packs < 0 then tk.node
        else null 
      end as node
    from scm.flow tr
    -- 仅保留能关联到内网交易的记录，避免生成无效0值行，如果需要保留所有flow记录可改回left join，后面加过滤条件it.trans_type is not null
    inner join scm.internal it
      on tr.id1 = it.id1 
      and tr.trans_t_no = it.trans_t_no 
      and tr.int_flag = '1' 
      and tr.description like 'GI%'
    left join scm.node_names kt
      on kt.code = it.rec_from_code
    left join scm.node_names tk
      on tk.code = it.sent_from_code
    where tr.int_flag = '1'
  ) t
  group by id1, id2, id3, nvl(node, 'Failed to record'), period
) k
-- 过滤掉收发数量均为0的无效行
where received > 0 or sent < 0;

第二步：额外验证逻辑 #

如果修复后仍存在重复，可执行以下查询定位重复行：

select id1, id2, id3, node, period, count(*)
from 你的视图名
group by id1, id2, id3, node, period
having count(*) > 1;

核对重复行的明细数据，确认是否存在scm.internal表中同交易号对应多条重复记录的情况，如有可先对scm.internal表按id1、trans_t_no去重后再关联。

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