我需要帮助，在这个数据集中每个作业只检索一条记录。实际结果集中有900多行，所以这是一个小样本，但所有行都如下所示。我已经在查询中使用了Distinct，但我得到了多个记录相同的作业，后缀和部分。我需要能够只拉一个工作，后缀，部分，但我需要每个值为workcenter,hours_estimated，和hours_actual与每个工作相关联。我不确定这是否应该/可以在查询或在PHP中使用数组方法。我几乎找到了我要找的东西，但普适不使用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 |
我需要我的PDO对象/数组看起来像
|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 2.5700 |
|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'
我必须改变我的查询
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,
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'
group by Job,v_job_header.part,v_job_header.qty_order,gab_source_cause_codes.source,gab_source_cause_codes.cause