oracle多行合并一行且排序

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;

转载于:https://www.cnblogs.com/oldcat/archive/2011/09/17/2179812.html