listagg 字符串连接的结果过长问题解决

最新推荐文章于 2024-10-18 11:57:52 发布

不急god

最新推荐文章于 2024-10-18 11:57:52 发布

阅读量7.4k

点赞数 2

CC 4.0 BY-SA版权

分类专栏：数据库

本文链接：https://blog.youkuaiyun.com/sdzhangshulong/article/details/87249592

数据库专栏收录该内容

13 篇文章

订阅专栏

本文分享了一次SQL查询优化的经验，对比了使用listagg和xmlagg函数在数据聚合方面的效果，通过实际案例展示了如何针对特定需求调整SQL语句，实现更高效的数据检索。

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

原sql

SELECT
  id agentId,
  AGENCYNAME agencyname,
  listagg(region, ',')
  WITHIN GROUP (
   ORDER BY AGENCYNAME) AS region



		--	xmlagg(xmlparse(content region||',' wellformed) order by AGENCYNAME).getclobval() AS region


FROM (

  SELECT
    t4.id,
    t4.AGENCYNAME,
    t4.REGIONNAME2 || '-' || t4.REGIONNAME region
  FROM (

         SELECT
           t.AGENCYNAME,
           t.id,
           t2.REGIONNAME,
           t3.REGIONNAME REGIONNAME2
         FROM STANIC_AGENCY t
           LEFT JOIN STANIC_AGENCYREGION t1
             ON t1.AGENCYID = t.ID
           INNER JOIN STANIC_REGION t2
             ON t1.CITYID = t2.ID
           LEFT JOIN STANIC_REGION t3
             ON t1.PROVINCEID = t3.ID

         WHERE t.PARENTID = '12b234'
			and t.status=1 and t.delflag=0
       ) t4

)
GROUP BY AGENCYNAME, id

修改后sql

	
SELECT
  id agentId,
  AGENCYNAME agencyname,
  --listagg(region, ',')
 -- WITHIN GROUP (
  --  ORDER BY AGENCYNAME) AS region



			xmlagg(xmlparse(content region||',' wellformed) order by AGENCYNAME).getclobval() AS region


FROM (

  SELECT
    t4.id,
    t4.AGENCYNAME,
    t4.REGIONNAME2 || '-' || t4.REGIONNAME region
  FROM (

         SELECT
           t.AGENCYNAME,
           t.id,
           t2.REGIONNAME,
           t3.REGIONNAME REGIONNAME2
         FROM STANIC_AGENCY t
           LEFT JOIN STANIC_AGENCYREGION t1
             ON t1.AGENCYID = t.ID
           INNER JOIN STANIC_REGION t2
             ON t1.CITYID = t2.ID
           LEFT JOIN STANIC_REGION t3
             ON t1.PROVINCEID = t3.ID

         WHERE t.PARENTID = 'edb'
			and t.status=1 and t.delflag=0
       ) t4

)
GROUP BY AGENCYNAME, id

测试完美解决