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

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/sdzhangshulong/article/details/87249592

原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



测试完美解决

展开阅读全文

没有更多推荐了,返回首页