LISTAGG函數怎么使用

這篇文章主要介紹“LISTAGG函數怎么使用”，在日常操作中，相信很多人在LISTAGG函數怎么使用問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”LISTAGG函數怎么使用”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！

LISTAGG函數 Oracle 11.2之後推出的：字符串聚合

字符串聚合就是按照分組把多行數據串聯成一行，以下面的結果集為例：

11.2之前有WMSYS.WM_CONCAT函數，不過是非文檔說明的

舉個列子 

為實現，同一類不同行數據 在 一行中顯示出來，可以使用

wmsys.wm_concat (a.FAILURE_DESC||'-'|| a.FAILURE_QTY)

或

LISTAGG(a.FAILURE_DESC||'-'|| a.FAILURE_QTY)WITHIN GROUP(ORDER BY NULL) 

select a.shift, a.work_order, a.machineid, a.date_work, a.part_name, a.model_name,a.CUSTOMER_NAME,

a.model_serial,   a.QTY,TO_CHAR(wmsys.wm_concat(a.FAILURE_DESC||'-'|| a.FAILURE_QTY)) from (

SELECT   a.shift, a.work_order, a.machineid, a.date_work, a.part_name, a.model_name,a.CUSTOMER_NAME,

a.model_serial, SUM (a.pass_qty)  QTY,b.FAILURE_DESC,b.FAILURE_QTY

FROM (SELECT a.shift, a.work_order, a.machineid,c.CUSTOMER_NAME,

CASE WHEN a.shift = 'N' AND a.time_section  <8

   THEN TO_CHAR (TO_DATE (a.work_date, 'yyyy/mm/dd') - 1,'yyyymmdd')

ELSE a.work_date

END AS date_work,

a.pass_qty, b.part_name, a.model_name, b.model_serial

FROM (SELECT work_date, work_order, machineid, model_name,

CASE WHEN time_section  > 8

   AND time_section <=20

   THEN 'D'

ELSE 'N'

END shift,time_section, pass_qty

FROM wip_d_machine_uph) a,mms.bdm_s_product b,BDM_S_CUSTOMER c

WHERE a.model_name = b.part_no and b.CUSTOMER_ID = c.CUSTOMER_ID)a,

(select a.*,

CASE WHEN time_section  > 8 AND time_section <= 20

THEN 'D'

ELSE 'N'

END shift ,

CASE WHEN time_section > 8

     AND time_section<= 20

   THEN to_char(FAILURE_INIPUT_TIME,'yyyymmdd')

ELSE to_char(FAILURE_INIPUT_TIME-1,'yyyymmdd')

END work_date                       

from OEE_D_FAILURE_INPUT a) b

where    a.WORK_ORDER = b.WORK_ORDER(+)

and a.model_name = b.PART_NO(+)      

and a.MACHINEID = b.MACHINE_ID(+)

and a.DATE_WORK = b.work_date(+)

and a.shift=b.shift(+)

and a.DATE_WORK>='20130101' and a.DATE_WORK<='20130203' and a.CUSTOMER_NAME ='DELL'      

GROUP BY a.shift,a.work_order,a.machineid,a.date_work,a.part_name,a.model_name,a.model_serial,a.CUSTOMER_NAME,b.FAILURE_DESC,b.FAILURE_QTY) a        

GROUP BY a.shift,a.work_order,a.machineid,a.date_work,a.part_name,a.model_name,a.qty,a.model_serial,a.CUSTOMER_NAME

到此，關于“LISTAGG函數怎么使用”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！