❤️oracle listagg() 函数用法（参照例子）
oracle listagg() 函数用法（参照例子）
LISTAGG 是一个 SQL 聚合函数，用于将一列的值连接成一个单一的字符串，以指定的分隔符分隔每个值。这在将多个值合并为一个字符串时非常有用，例如在生成逗号分隔的值列表或用于报告生成等情况下。
以下是 LISTAGG 函数的基本语法：
LISTAGG(column_name, separator) WITHIN GROUP (ORDER BY ordering_expression)
column_name是要连接的列名。separator是用于分隔每个值的字符串。ORDER BY ordering_expression可选，用于对要连接的值进行排序。
示例用法：
假设有以下表 employees：
| id | name |
|---|---|
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
你可以使用 LISTAGG 函数来连接员工姓名，并使用逗号作为分隔符：
SELECT LISTAGG(name, ', ') WITHIN GROUP (ORDER BY id) AS concatenated_names
FROM employees;
这将返回一个结果，其中所有员工的姓名被连接起来，使用逗号和空格作为分隔符：
concatenated_names
---------------------
Alice, Bob, Charlie
请注意，LISTAGG 函数在不同的数据库管理系统中可能有所不同，因此具体的语法和用法可能会有所不同。上述示例适用于一些主流的数据库系统，如 Oracle Database。
工作中经常遇到很多需求是这样的，根据条件汇总某些字段，比如公司有三个投资平台，同一个客户拿手机号在三个平台都注册了，但注册过的用户名不一样，显示的时候需要根据手机号显示所有注册过的名称。(我用的是oracle数据库)
1、原始数据是这样的，如图：
2、要求显示成这样，如图：
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'