LISTAGG函數(shù)怎么使用

206次閱讀

共計(jì) 2008 個(gè)字符，預(yù)計(jì)需要花費(fèi) 6 分鐘才能閱讀完成。

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

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

字符串聚合就是按照分組把多行數(shù)據(jù)串聯(lián)成一行，以下面的結(jié)果集為例：

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

舉個(gè)列子

為實(shí)現(xiàn)，同一類不同行數(shù)據(jù) 在一行中顯示出來，可以使用

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

到此，關(guān)于“LISTAGG 函數(shù)怎么使用”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請(qǐng)繼續(xù)關(guān)注丸趣 TV 網(wǎng)站，丸趣 TV 小編會(huì)繼續(xù)努力為大家?guī)砀鄬?shí)用的文章！

正文完