View Code SELECT lotid, ltrim ( MAX (sys_connect_by_path(wf_lotno, ' , ' )), ' , ' ) wf_lotno, ltrim ( MAX (sys_connect_by_path(wf_jobno, ' , ' )), ' , ' ) wf_jobno, ltrim ( MAX (sys_connect_by_path(wf_partno, ' , ' )), ' , ' ) wf_partno, ltrim ( MAX (sys_connect_by_path(supplier, ' , ' )), ' , ' ) supplier, ltrim ( MAX (sys_connect_by_path(wf_id, ' , ' )), ' , ' ) wf_id FROM ( SELECT lotid, wf_lotno, wf_jobno, wf_partno, supplier, wf_id, MIN (wf_id) over (PARTITION BY lotid) wf_id_min, (row_number() over ( ORDER BY lotid, wf_id)) + (dense_rank() over ( ORDER BY lotid)) wf_id_count FROM ( SELECT lotid, wf_lotno, wf_jobno, wf_partno, supplier, to_string( CAST (COLLECT(wf_id) AS varchar2_ntt)) AS wf_id FROM dio_yld_wf_inf_w WHERE lotid = ' B737756.D1 ' GROUP BY lotid, wf_lotno, wf_jobno, wf_partno, supplier)) START WITH wf_id = wf_id_minCONNECT BY wf_id_count - 1 = PRIOR wf_id_count GROUP BY lotid;
第一层：相同wf_lot先合并一次。
比如
lotid wf_lot wf_id
10 a 5
10 a 6
10 b 7
11 a 8
11 b 9
12 a 10
第一层结果为
10 a 5 / 6
10 b 7
11 a 8
11 b 9
12 a 10
希望达到的最终效果：
10 a,b 5 / 6,7
11 a,b 8,9
12 a 10
注意: 10这一行不能是:
10 a,b 7,5 / 6
或
10 b,a 5 / 6, 7
要按顺序排列。
暂时看这个方法的效率不是很好。
后面改进了一下：
SELECT lotid, MAX (wf_lotno) wf_lotno, MAX (wf_jobno) wf_jobno, MAX (wf_partno) wf_partno, MAX (supplier) supplier, MAX (wf_id) wf_id FROM ( SELECT t.lotid, wmsys.wm_concat(t.wf_lotno) over (PARTITION BY t.lotid ORDER BY t.lotid, t.wf_lotno) wf_lotno, wmsys.wm_concat(t.wf_jobno) over (PARTITION BY t.lotid ORDER BY t.lotid, t.wf_jobno) wf_jobno, wmsys.wm_concat(t.wf_partno) over (PARTITION BY t.lotid ORDER BY t.lotid, t.wf_lotno, t.wf_jobno) wf_partno, wmsys.wm_concat(t.supplier) over (PARTITION BY t.lotid ORDER BY t.lotid, t.wf_lotno, t.supplier) supplier, wmsys.wm_concat(t.wf_id) over (PARTITION BY t.lotid ORDER BY t.lotid, t.wf_lotno, t.wf_id) wf_id FROM ( SELECT lotid, wf_lotno, wf_jobno, wf_partno, supplier, to_string( CAST (COLLECT(wf_id) AS varchar2_ntt)) AS wf_id FROM dio_yld_wf_inf_w /* WHERE lotid = 'B737756.D1' */ GROUP BY lotid, wf_lotno, wf_jobno, wf_partno, supplier) t) GROUP BY lotid;
继续改进：
SELECT lotid, MAX (wf_lotno) wf_lotno, MAX (wf_jobno) wf_jobno, MAX (wf_partno) wf_partno, MAX (supplier) supplier, MAX (wf_id) wf_id FROM ( SELECT t.lotid, wmsys.wm_concat(t.wf_lotno) over (PARTITION BY t.lotid ORDER BY t.lotid, t.wf_lotno) wf_lotno, wmsys.wm_concat(t.wf_jobno) over (PARTITION BY t.lotid ORDER BY t.lotid, t.wf_jobno) wf_jobno, wmsys.wm_concat(t.wf_partno) over (PARTITION BY t.lotid ORDER BY t.lotid, t.wf_lotno, t.wf_jobno) wf_partno, wmsys.wm_concat(t.supplier) over (PARTITION BY t.lotid ORDER BY t.lotid, t.wf_lotno, t.supplier) supplier, wmsys.wm_concat(t.wf_id) over (PARTITION BY t.lotid ORDER BY t.lotid, t.wf_lotno, t.wf_id) wf_id FROM ( SELECT lotid, wf_lotno, wf_jobno, wf_partno, supplier, to_string( CAST (COLLECT(wf_id) AS varchar2_ntt)) AS wf_id FROM dio_yld_wf_inf_w /* WHERE lotid = 'B737756.D1' */ GROUP BY lotid, wf_lotno, wf_jobno, wf_partno, supplier) t) GROUP BY lotid;