Pervasive/PHP环境下按Job分组获取单条聚合记录的方法

需要实现数据集中每个唯一Job+suffix+part组合仅返回1条记录，当前结果集共900余行，已在查询中使用DISTINCT关键字，但同组合下仍存在多条重复记录。最终需要保留每个组合下所有关联的workcenter、hours_estimated、hours_actual字段值，暂不确定该逻辑在SQL查询层实现还是通过PHP数组方法处理；由于使用的Pervasive数据库不支持CTE语法，无法直接复用常规分组拼接方案。

|Job   |suffix| part |PL|qty|  seq |workcenter|hours_estimated|hours_actual|
----------------------------------------------------------------------------
|A02043| 001  |913036|01| 2 |000400|   0710   |     0.7491    |   2.5700   |
|A02043| 001  |913036|01| 2 |000402|   0805   |     0.6420    |   0.0000   |
|A02043| 001  |913036|01| 2 |000500|   0901   |     16.1290   |   33.1600  |
|A02043| 001  |913036|01| 2 |000600|   1520   |     0.5000    |   0.0000   |
|A02900| 001  |913104|01| 1 |000500|   0710   |     0.5280    |   1.2000   |
|A02900| 001  |913104|01| 1 |000600|   0650   |     0.8540    |   0.0000   |

|Job   |suffix| part |PL|qty|   workcenter      |     hours_estimated         | hours_actual             |
-------------------------------------------------------------------------------------------------------------------
|A02043| 001  |913036|01| 2 |0710,0805,0901,1520| 0.7491, 0.6420,16.1290,0.500| 2.57,0,33.16,0           |
|A02900| 001  |913104|01| 1 |0710,0650          |0.5280,0.8540                |1.200,0.0000              |  

select distinct v_job_header.job,v_job_header.suffix,v_job_header.part,v_job_header.product_line,v_job_header.qty_order,v_job_operations_wc.seq,v_job_operations_wc.LMO,v_job_operations_wc.workcenter,v_job_operations_wc.hours_estimated,v_job_operations_wc.hours_actual,v_job_operations_wc.flag_closed,gab_source_cause_codes.source,gab_source_cause_codes.cause
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
left join gab_source_cause_codes on gab_source_cause_codes.job = v_job_operations_wc.job and gab_source_cause_codes.suffix = v_job_operations_wc.suffix and gab_source_cause_codes.seq = v_job_operations_wc.seq
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'

两种方案均可实现需求，优先推荐PHP处理方案，兼容性更强、调试成本更低：

• 方案1：PHP数组处理（推荐）
  直接使用现有SQL查询出所有明细数据，在PHP层按Job+suffix+part维度分组聚合，代码逻辑如下：

  <?php
  // 假设$pdo查询后得到的所有明细数据存在$rawList数组中
  $grouped = [];
  foreach ($rawList as $row) {
      // 生成唯一分组键
      $key = $row['job'] . '_' . $row['suffix'] . '_' . $row['part'];
      if (!isset($grouped[$key])) {
          // 初始化分组，保留固定字段
          $grouped[$key] = [
              'job' => $row['job'],
              'suffix' => $row['suffix'],
              'part' => $row['part'],
              'product_line' => $row['product_line'],
              'qty_order' => $row['qty_order'],
              'workcenter' => [],
              'hours_estimated' => [],
              'hours_actual' => []
          ];
      }
      // 追加同组下的多值字段
      $grouped[$key]['workcenter'][] = $row['workcenter'];
      $grouped[$key]['hours_estimated'][] = $row['hours_estimated'];
      $grouped[$key]['hours_actual'][] = $row['hours_actual'];
  }
  // 最后将数组转成逗号分隔的字符串格式
  foreach ($grouped as &$item) {
      $item['workcenter'] = implode(',', $item['workcenter']);
      $item['hours_estimated'] = implode(',', $item['hours_estimated']);
      $item['hours_actual'] = implode(',', $item['hours_actual']);
  }
  unset($item);
  // $grouped即为最终需要的结构
  

  该方案不受数据库语法限制，900行数据量下处理耗时可忽略，后续如果需要调整字段拼接顺序、去重规则也更灵活。

• 方案2：Pervasive SQL层聚合
  Pervasive PSQL从v11版本开始支持LIST()聚合函数，可以直接在SQL层完成字符串拼接，改写后的SQL如下：

  select 
      v_job_header.job,
      v_job_header.suffix,
      v_job_header.part,
      v_job_header.product_line,
      v_job_header.qty_order,
      LIST(v_job_operations_wc.workcenter, ',') as workcenter,
      LIST(v_job_operations_wc.hours_estimated, ',') as hours_estimated,
      LIST(v_job_operations_wc.hours_actual, ',') as hours_actual
  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
  left join gab_source_cause_codes on gab_source_cause_codes.job = v_job_operations_wc.job and gab_source_cause_codes.suffix = v_job_operations_wc.suffix and gab_source_cause_codes.seq = v_job_operations_wc.seq
  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 v_job_header.job,v_job_header.suffix,v_job_header.part,v_job_header.product_line,v_job_header.qty_order
  

  注意：如果使用的Pervasive版本低于v11不支持LIST()函数，直接选择PHP处理方案即可，不要在低版本SQL里写自定义拼接逻辑，复杂度高且容易出错。

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