oracle: 1.LISTAGG将一列拼接为一个字段；2.LISTAGG返回值超过4000解决办法（用xmlagg( ).getclobval() ）

最新推荐文章于 2024-05-07 14:11:46 发布

置顶付月半子

最新推荐文章于 2024-05-07 14:11:46 发布

阅读量2.8k

点赞数 2

分类专栏： Sql语句数据库

本文链接：https://blog.youkuaiyun.com/qq_34169240/article/details/98033209

版权

Sql语句同时被 2 个专栏收录

17 篇文章

订阅专栏

数据库

9 篇文章

订阅专栏

本文详细介绍了在Oracle数据库中如何使用LISTAGG函数聚合字符串，并解决其长度限制的问题，通过使用XMLAGG函数提供了一种替代方案，适用于需要处理大量数据的场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1. LISTAGG(RISK_FLAG, ',') WITHIN GROUP (ORDER BY END_TIME,ID)

 SELECT A.EQUIPMENTCODE,LISTAGG(RISK_FLAG, '') WITHIN GROUP (ORDER BY END_TIME,ID) AS RISK_FLAG_ALL FROM(

								
				SELECT  10051  ID  ,'10V-LTZ-EHZB-AX-BT' EQUIPMENTCODE,'2019-02-03' END_TIME,'N'  RISK_FLAG  FROM DUAL
				UNION
				SELECT  10012  ID  ,'10V-LTZ-EHZB-AX-BT' EQUIPMENTCODE,'2019-02-05' END_TIME,'Y'  RISK_FLAG  FROM DUAL
				UNION
				SELECT  10011  ID  ,'10V-LTZ-EHZB-AX-BT' EQUIPMENTCODE,'2019-05-03' END_TIME,'N'  RISK_FLAG  FROM DUAL
				UNION
				SELECT  10061  ID  ,'10V-LTZ-EHZB-AX-BT' EQUIPMENTCODE,'2019-05-03' END_TIME,'N'  RISK_FLAG  FROM DUAL
				UNION
				SELECT  11011  ID  ,'10V-LTZ-EHZB-BX-BT' EQUIPMENTCODE,'2019-05-03' END_TIME,'N'  RISK_FLAG  FROM DUAL
				 
 	) A  GROUP BY A.EQUIPMENTCODE

2. LISTAGG返回值是varchar类型，不能超过4000.一旦超过4000，就会报错

这种情况下，要改写LISTAGG，如下：

 SELECT A.EQUIPMENTCODE,
       --LISTAGG(RISK_FLAG, '') WITHIN GROUP (ORDER BY END_TIME,ID) AS RISK_FLAG_ALL,
	      xmlagg(xmlparse(content RISK_FLAG||'' wellformed) order by END_TIME,ID).getclobval()  AS  RISK_FLAG_ALL2
	FROM(
				SELECT  10051  ID  ,'10V-LTZ-EHZB-AX-BT' EQUIPMENTCODE,'2019-02-03' END_TIME,'N'  RISK_FLAG  FROM DUAL
				UNION
				SELECT  10012  ID  ,'10V-LTZ-EHZB-AX-BT' EQUIPMENTCODE,'2019-02-05' END_TIME,'Y'  RISK_FLAG  FROM DUAL
				UNION
				SELECT  10011  ID  ,'10V-LTZ-EHZB-AX-BT' EQUIPMENTCODE,'2019-05-03' END_TIME,'N'  RISK_FLAG  FROM DUAL
				UNION
				SELECT  10061  ID  ,'10V-LTZ-EHZB-AX-BT' EQUIPMENTCODE,'2019-05-03' END_TIME,'N'  RISK_FLAG  FROM DUAL
				UNION
				SELECT  11011  ID  ,'10V-LTZ-EHZB-BX-BT' EQUIPMENTCODE,'2019-05-03' END_TIME,'N'  RISK_FLAG  FROM DUAL
) A  GROUP BY A.EQUIPMENTCODE