❤️oracle listagg() 函数用法(参照例子)

小明的学习圈子2023-08-13数据库Oracle

oracle listagg() 函数用法(参照例子)

LISTAGG 是一个 SQL 聚合函数,用于将一列的值连接成一个单一的字符串,以指定的分隔符分隔每个值。这在将多个值合并为一个字符串时非常有用,例如在生成逗号分隔的值列表或用于报告生成等情况下。

以下是 LISTAGG 函数的基本语法:

LISTAGG(column_name, separator) WITHIN GROUP (ORDER BY ordering_expression)
  • column_name 是要连接的列名。
  • separator 是用于分隔每个值的字符串。
  • ORDER BY ordering_expression 可选,用于对要连接的值进行排序。

示例用法:

假设有以下表 employees

idname
1Alice
2Bob
3Charlie

你可以使用 LISTAGG 函数来连接员工姓名,并使用逗号作为分隔符:

SELECT LISTAGG(name, ', ') WITHIN GROUP (ORDER BY id) AS concatenated_names
FROM employees;

这将返回一个结果,其中所有员工的姓名被连接起来,使用逗号和空格作为分隔符:

concatenated_names
---------------------
Alice, Bob, Charlie

请注意,LISTAGG 函数在不同的数据库管理系统中可能有所不同,因此具体的语法和用法可能会有所不同。上述示例适用于一些主流的数据库系统,如 Oracle Database。

工作中经常遇到很多需求是这样的,根据条件汇总某些字段,比如公司有三个投资平台,同一个客户拿手机号在三个平台都注册了,但注册过的用户名不一样,显示的时候需要根据手机号显示所有注册过的名称。(我用的是oracle数据库)

1、原始数据是这样的,如图:

1

2、要求显示成这样,如图:

1

3、具体实现:

select phone, listagg(log_name, ',') within group(order by phone) logName from int_phone where phone = '13350162230' group by phone

4、需要注意的事项如下: (1). 必须得分组,也就是说group by是必须的。 (2). listagg函数的第一个参数是需要显示的字段,也就是log_name;第二个参数是数值之间的分隔符;同时还需要进行排序和分组within group (order by name)

例子

SELECT
	( SELECT PARTY_VALUE FROM T_TRANSFORM_DICT ttd5 WHERE HRS_TYPE = 'view_zdxzzw' AND HRS_VALUE = ZYJSZW.ZYJSZWJBM ) AS POST_RANK,
	( SELECT PARTY_VALUE FROM T_TRANSFORM_DICT ttd5 WHERE HRS_TYPE = 'view_zyjszw' AND HRS_VALUE = ZYJSZW.RZZGMCM ) AS RZZGMCM,
	ZYJSZW.PRQSRQ AS PRQSRQ,
	cs.DUTIES AS xrzw,
	cs.TENURE_YEAR || NVL2 ( cs.TENURE_YEAR, '-', '' ) || cs.TENURE_MONTH || NVL2 ( cs.TENURE_MONTH, '-', '' ) || cs.TENURE_DAY AS RENZHISJ,
	gbxx.RTJSJ,
	qp.PRESERVE01,
	( CASE WHEN qp.QUFEN_FLAG = '1' THEN '专职党政' WHEN qp.QUFEN_FLAG = '2' THEN '双肩挑' ELSE '' END ) AS QUFEN_FLAG,
	QP.CREATE_DATE,
	QP.QUASI_ID,
	QP.DEL_FLAG,
	QP.LS,
	QP.HYTJ_ID,
	QP.NUM_ID AS HXMD_NUM_ID,
	RP.ZGH,
	RP.NUM_ID AS PER_NUM_ID,
	RP.XM,
	RP.JGM,
	( SELECT PARTY_VALUE FROM T_TRANSFORM_DICT WHERE HRS_TYPE = 'view_xb' AND HRS_VALUE = rp.XBM ) AS XB,
	rp.csrq,
	TRUNC ( MONTHS_BETWEEN ( SYSDATE, CSRQ ) / 12 ) AS AGE,
	( SELECT PARTY_VALUE FROM T_TRANSFORM_DICT WHERE HRS_TYPE = 'view_zzmm' AND HRS_VALUE = zp.POLITICAL ) AS zzmm,
	cs.TENURE_YEAR || NVL2 ( cs.TENURE_YEAR, '-', '' ) || cs.TENURE_MONTH || NVL2 ( cs.TENURE_MONTH, '-', '' ) || cs.TENURE_DAY AS TENURE,
	rp.num_id AS emp_id,
	( SELECT PARTY_VALUE FROM T_TRANSFORM_DICT WHERE HRS_TYPE = 'view_mz' AND HRS_VALUE = rp.MZM ) AS MZMV,
	( SELECT PARTY_VALUE FROM T_TRANSFORM_DICT WHERE HRS_TYPE = 'view_xzdq' AND HRS_VALUE = rp.JGM ) AS JGMV,
	( SELECT PARTY_VALUE FROM T_TRANSFORM_DICT WHERE HRS_TYPE = 'view_zyjszw' AND HRS_VALUE = zp.POLITICAL ) AS ZYJSZC,
	ao.NAME AS DWHN,
	(
	SELECT
		listagg (
			TAO.NAME ||
		CASE
				
				WHEN NVL ( TAO.NAME, NULL ) IS NULL 
				OR NVL ( TCS.DUTIES, NULL ) IS NULL THEN
					'' ELSE '' 
					END || TCS.DUTIES,
				',' 
			) WITHIN GROUP ( ORDER BY TCS.SERIAL_NUMBER ) DUTIES 
		FROM
			T_CADRE_SELECTION TCS
			LEFT JOIN T_ADMIN_ORG TAO ON TCS.TENURE_UNIT = TAO.NUM_ID 
		WHERE
			TCS.base_info_id = rp.NUM_ID 
			AND TCS.DEL_FLAG = '0' 
			AND Tcs.INCUMBENT = '1' 
		) AS DUTIES,
		zp.JOIN_DATE rdrq,
		( SELECT PARTY_VALUE FROM T_TRANSFORM_DICT WHERE HRS_TYPE = 'view_xl' AND HRS_VALUE = XXJL.XLM ) AS ZGXL,
		( SELECT PARTY_VALUE FROM T_TRANSFORM_DICT WHERE HRS_TYPE = 'view_xw' AND HRS_VALUE = XXJL1.HDXWM ) AS ZGXW,
		(
		SELECT
			listagg (
				TO_CHAR ( tab.RXNY, 'yyyy-mm' ) ||
			CASE
					
					WHEN tab.RXNY IS NULL THEN
					'' ELSE ' ~ ' 
				END || TO_CHAR ( tab.JSXYNY, 'yyyy-mm' ) || ' ' || tab.BSYXXHDW || ' ' || tab.HXWZYM_NAME || ' ' || tab.XLM_NAME,
				CHR ( 10 ) 
			) WITHIN GROUP ( ORDER BY tab.BSYXXHDW ) 
		FROM
			(
			SELECT
				XXJL.*,
				( SELECT label FROM SYS_DICT WHERE TYPE = 'SXZY' AND VALUE = XXJL.HXWZYM ) AS HXWZYM_NAME,
				(
				SELECT
					sd.label 
				FROM
					SYS_DICT sd,
					T_TRANSFORM_DICT ttd 
				WHERE
					sd.TYPE = ttd.PARTY_TYPE 
					AND sd.
				VALUE
					= ttd.PARTY_VALUE 
					AND ttd.HRS_TYPE = 'view_xl' 
					AND ttd.HRS_VALUE = XXJL.XLM 
				) AS XLM_NAME,
				XXJL.JYLBM AS JYLBM_VALUE,-- 教育类别
				( SELECT PARTY_VALUE FROM T_TRANSFORM_DICT WHERE HRS_TYPE = 'view_xl' AND HRS_VALUE = XXJL.XLM ) AS XLM_VALUE,-- 学历
				( SELECT PARTY_VALUE FROM T_TRANSFORM_DICT WHERE HRS_TYPE = 'view_xw' AND HRS_VALUE = XXJL.HDXWM ) AS HDXWM_VALUE,-- 学位
				XXJL.SXWGJDQM AS SXWGJDQM_VALUE,
				( SELECT label FROM SYS_DICT WHERE TYPE = 'XXXS' AND VALUE = XXJL.XXXSM ) AS XXXSM_NAME 
			FROM
				V_MID_RS_XXJL_PARTY XXJL
				LEFT JOIN V_MID_RS_PARTY RS ON RS.ZGH = XXJL.ZGH 
			WHERE
				xxjl.zgh = qp.QUASI_ID 
			) tab 
		) AS xxjlaa,
		(
		SELECT
			listagg (
				TO_CHAR ( cad.gzqsrq, 'yyyy-mm' ) ||
			CASE
					
					WHEN cad.gzqsrq IS NULL THEN
					'' ELSE ' ~ ' 
				END || TO_CHAR ( cad.GZZZRQ, 'yyyy-mm' ) || ' ' || cad.GZDW || ' ' -- 如果专业技术职务码有值
				||
			CASE
					
					WHEN cad.zyjszwm IS NULL THEN
					cad.dzzw -- 如果没值显示党政职务
					ELSE SD.LABEL 
				END,
				CHR ( 10 ) 
			) WITHIN GROUP ( ORDER BY cad.ZGH ) AS qqq 
		FROM
			T_MID_RS_GZJL_PARTY cad
			LEFT JOIN SYS_DICT SD ON SD.
		VALUE
			= cad.zyjszwm 
			AND SD.TYPE = 'ZYJSZW' 
		WHERE
			cad.zgh = qp.QUASI_ID 
		) GZDW 
	FROM
		T_CADRE_HYTJ_HXMD qp
		INNER JOIN V_MID_RS_PARTY rp ON QP.QUASI_ID = RP.ZGH
		LEFT JOIN T_ADMIN_ORG ao ON ao.NUM_ID = rp.DWH
		LEFT JOIN (
		SELECT
			* 
		FROM
			T_EMPLOYEE_BASE_INFO ZZMM
			INNER JOIN T_P_BASE_INFO TPBI ON TPBI.EMP_ID = ZZMM.NUM_ID 
			AND TPBI.HISTORY_FLAG = '0' 
		WHERE
			ZZMM.STAFF_TYPE = '0' 
		) zp ON zp.EMPLOYEE_CODE = rp.ZGH
		LEFT JOIN T_CADRE_SELECTION cs ON cs.EMP_ID = qp.QUASI_ID 
		AND cs.DEL_FLAG = '0' 
		AND SERIAL_NUMBER = '1'
		LEFT JOIN T_CADRE_USER_INFO gbxx ON gbxx.BASE_INFO_ID = cs.BASE_INFO_ID 
		AND gbxx.FORMAL = '0' 
		AND gbxx.HISTORICAL_CADRES = '0' 
		AND gbxx.DEL_FLAG = '0' --L EFT
		JOIN T_MID_RS_ZZMM_PARTY ZZMM ON qp.QUASI_ID = ZZMM.ZGH 
		AND ZZMM.SFDQ = '1'
		LEFT JOIN V_MID_RS_ZYJSZW_PARTY ZYJSZW ON ZYJSZW.ZGH = qp.QUASI_ID 
		AND ZYJSZW.SFDQ = '1'
		LEFT JOIN V_MID_RS_XXJL_PARTY XXJL ON XXJL.ZGH = qp.QUASI_ID 
		AND XXJL.SFZGXL = '1'
	LEFT JOIN V_MID_RS_XXJL_PARTY XXJL1 ON XXJL1.ZGH = qp.QUASI_ID 
	AND XXJL1.SFZGXW = '1'
Last Updated 2024/4/6 10:47:15