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(SELECTlotid,
wf_lotno,
wf_jobno,
wf_partno,
supplier,
wf_id,
MIN(wf_id)over(PARTITIONBYlotid)wf_id_min,
(row_number()over(ORDERBYlotid,wf_id))+
(dense_rank()over(ORDERBYlotid))wf_id_count
FROM(SELECTlotid,
wf_lotno,
wf_jobno,
wf_partno,
supplier,
to_string(CAST(COLLECT(wf_id)ASvarchar2_ntt))ASwf_id
FROMdio_yld_wf_inf_w
WHERElotid='B737756.D1'GROUPBYlotid,
wf_lotno,
wf_jobno,
wf_partno,
supplier))
STARTWITHwf_id=wf_id_min
CONNECTBYwf_id_count-1=PRIORwf_id_count
GROUPBYlotid;
第一层:相同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
要按顺序排列。
暂时看这个方法的效率不是很好。
后面改进了一下:
MAX(wf_lotno)wf_lotno,
MAX(wf_jobno)wf_jobno,
MAX(wf_partno)wf_partno,
MAX(supplier)supplier,
MAX(wf_id)wf_id
FROM(SELECTt.lotid,
wmsys.wm_concat(t.wf_lotno)over(PARTITIONBYt.lotidORDERBYt.lotid,t.wf_lotno)wf_lotno,
wmsys.wm_concat(t.wf_jobno)over(PARTITIONBYt.lotidORDERBYt.lotid,t.wf_jobno)wf_jobno,
wmsys.wm_concat(t.wf_partno)over(PARTITIONBYt.lotidORDERBYt.lotid,t.wf_lotno,t.wf_jobno)wf_partno,
wmsys.wm_concat(t.supplier)over(PARTITIONBYt.lotidORDERBYt.lotid,t.wf_lotno,t.supplier)supplier,
wmsys.wm_concat(t.wf_id)over(PARTITIONBYt.lotidORDERBYt.lotid,t.wf_lotno,t.wf_id)wf_id
FROM(SELECTlotid,
wf_lotno,
wf_jobno,
wf_partno,
supplier,
to_string(CAST(COLLECT(wf_id)ASvarchar2_ntt))ASwf_id
FROMdio_yld_wf_inf_w
/*WHERElotid='B737756.D1'*/GROUPBYlotid,
wf_lotno,
wf_jobno,
wf_partno,
supplier)t)
GROUPBYlotid;
继续改进:
MAX(wf_lotno)wf_lotno,
MAX(wf_jobno)wf_jobno,
MAX(wf_partno)wf_partno,
MAX(supplier)supplier,
MAX(wf_id)wf_id
FROM(SELECTt.lotid,
wmsys.wm_concat(t.wf_lotno)over(PARTITIONBYt.lotidORDERBYt.lotid,t.wf_lotno)wf_lotno,
wmsys.wm_concat(t.wf_jobno)over(PARTITIONBYt.lotidORDERBYt.lotid,t.wf_jobno)wf_jobno,
wmsys.wm_concat(t.wf_partno)over(PARTITIONBYt.lotidORDERBYt.lotid,t.wf_lotno,t.wf_jobno)wf_partno,
wmsys.wm_concat(t.supplier)over(PARTITIONBYt.lotidORDERBYt.lotid,t.wf_lotno,t.supplier)supplier,
wmsys.wm_concat(t.wf_id)over(PARTITIONBYt.lotidORDERBYt.lotid,t.wf_lotno,t.wf_id)wf_id
FROM(SELECTlotid,
wf_lotno,
wf_jobno,
wf_partno,
supplier,
to_string(CAST(COLLECT(wf_id)ASvarchar2_ntt))ASwf_id
FROMdio_yld_wf_inf_w
/*WHERElotid='B737756.D1'*/GROUPBYlotid,
wf_lotno,
wf_jobno,
wf_partno,
supplier)t)
GROUPBYlotid;
最后由原来20s降低到4s。