Pervasive SQL如何解决GROUP BY含source/cause列致同Job多行问题

不要尝试规避GROUP BY的非聚合字段必须加入分组的强制要求，这个规则是用来避免数据逻辑错误的，你当前查询返回多行的问题和规则本身无关，核心是查询的关联粒度设计错误：

• 现有查询中gab_source_cause_codes是按job+suffix+seq关联工序表，关联后的结果集粒度是单工序行，把source、cause加入GROUP BY后，同一个Job下只要不同seq对应的source、cause取值有差异（包括空值和非空值的差异），就会被拆成独立分组，最终返回多行，和单Job一行的预期不符。
• 不需要通过PHP处理结果集，直接在SQL层面调整逻辑就能实现预期效果。

把工时聚合、source/cause取值拆成两个逻辑层处理，避免source/cause字段干扰工时聚合的分组粒度：

1. 第一层子查询仅关联工单表和工序表，按Job、part、qty_order维度聚合所有工时数据，这一步完全不关联source/cause表，保证每个Job只返回一行聚合完成的工时结果
2. 外层查询基于已经聚合好的单Job工时结果，再关联gab_source_cause_codes表，用聚合函数取同一个Job下非空的source、cause值即可，和你样例里的预期逻辑一致：空值行和有source/cause的行合并，保留非空的原因码。

调整后的SQL如下：

select 
  job_agg.Job,
  job_agg.part,
  job_agg.qty_order,
  job_agg.WaterJet,
  job_agg.Laser,
  job_agg.Prep,
  job_agg.Machining,
  job_agg.Fab,
  job_agg.Paint,
  job_agg.Belts,
  job_agg.Electrical,
  job_agg.Crating_Skids,
  job_agg.Final_Assy,
  job_agg.Shipping,
  job_agg.total_hours_estimated,
  max(gab_source_cause_codes.source) as source,
  max(gab_source_cause_codes.cause) as cause
from (
  -- 子查询单独聚合工时，不受source/cause取值影响，保证每个Job仅返回一行
  select 
    concat(concat(v_job_header.job,'-'),v_job_header.suffix) as Job,
    v_job_header.part,
    v_job_header.qty_order,
    sum(case when v_job_operations_wc.workcenter = '0750' then v_job_operations_wc.hours_actual end) as WaterJet,
    sum(case when v_job_operations_wc.workcenter IN ('0705','0710','0715') then v_job_operations_wc.hours_actual end) as Laser,
    sum(case when v_job_operations_wc.workcenter IN ('0600','0610','1006','0650','1315') then v_job_operations_wc.hours_actual end) as Prep,
    sum(case when v_job_operations_wc.workcenter IN ('1310','0755') then v_job_operations_wc.hours_actual end) as Machining,
    sum(case when v_job_operations_wc.workcenter IN ('1515','1000','1002','1003','0901','1270') then v_job_operations_wc.hours_actual end) as Fab,
    sum(case when v_job_operations_wc.workcenter = '1100' then v_job_operations_wc.hours_actual end) as Paint,
    sum(case when v_job_operations_wc.workcenter = '1000' then v_job_operations_wc.hours_actual end) as Belts,
    sum(case when v_job_operations_wc.workcenter = '1001' then v_job_operations_wc.hours_actual end) as Electrical,
    sum(case when v_job_operations_wc.workcenter = '1520' then v_job_operations_wc.hours_actual end) as Crating_Skids,
    sum(case when v_job_operations_wc.workcenter IN ('1004','1005','1350','1201') then v_job_operations_wc.hours_actual end) as Final_Assy,
    sum(case when v_job_operations_wc.workcenter = '4330' then v_job_operations_wc.hours_actual end) as Shipping,
    sum(v_job_operations_wc.hours_estimated) as total_hours_estimated,
    v_job_header.job as raw_job,
    v_job_header.suffix as raw_suffix
  from v_job_header
  left join v_job_operations_wc 
    on v_job_operations_wc.job = v_job_header.job 
    and v_job_header.suffix = v_job_operations_wc.suffix
  where v_job_header.product_line = '01' 
    and v_job_header.date_closed < '2019-01-01' 
    and v_job_operations_wc.LMO = 'L' 
    and v_job_operations_wc.seq < '99000'
  group by Job, v_job_header.part, v_job_header.qty_order, raw_job, raw_suffix
) job_agg
left join gab_source_cause_codes 
  on gab_source_cause_codes.job = job_agg.raw_job 
  and gab_source_cause_codes.suffix = job_agg.raw_suffix
group by 
  job_agg.Job,
  job_agg.part,
  job_agg.qty_order,
  job_agg.WaterJet,
  job_agg.Laser,
  job_agg.Prep,
  job_agg.Machining,
  job_agg.Fab,
  job_agg.Paint,
  job_agg.Belts,
  job_agg.Electrical,
  job_agg.Crating_Skids,
  job_agg.Final_Assy,
  job_agg.Shipping,
  job_agg.total_hours_estimated

说明 #

• 该写法完全符合Pervasive SQL的GROUP BY语法要求，不需要修改数据库配置或者绕过语法校验
• 工时聚合在子查询内完成，不会被source、cause的取值差异拆分，每个Job只会返回一行
• 用max()函数取source、cause值会自动跳过空值，直接获取对应Job下存在的非空原因码，返回结果和你给出的预期完全一致
• 如果后续出现同一个Job对应多个不同source、cause的场景，只需要调整外层source、cause的取值规则即可（比如取最大seq对应的记录），不会影响核心工时聚合的准确性。

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