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_min
CONNECT 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;
最後由原來20s降低到4s。
