Oracle 将子查询内容拼接为字符串

最新推荐文章于 2024-04-01 17:31:21 发布

隶属感

最新推荐文章于 2024-04-01 17:31:21 发布

阅读量2.1k

点赞数

分类专栏： SQL Oracle数据库

本文链接：https://blog.csdn.net/qq_36445227/article/details/89684304

版权

SQL 同时被 2 个专栏收录

16 篇文章 0 订阅

订阅专栏

Oracle数据库

11 篇文章 0 订阅

订阅专栏

方法一 :缺点字符串太长会报错

SQL:


             select LINEID, AREAID, LINENAME, CARCODE, CARSIM, LINENOTES, LINESORTINDEX, AREANAME, AREASORTINDEX,  '[' || MAPPOINTS || ']' as MAPPOINTS from(  
          select   LINEID, LINE.AREAID, LINENAME, CARCODE, CARSIM, LINENOTES, LINESORTINDEX, areaname, areasortindex, ( 
              select   wm_concat('{ \"LPOINTSORTINDEX\":' || LPOINTSORTINDEX || ' ,\"lpointid\": ' || lpointid || ',\"lng\":' || lng || ',\"Lat\":' || Lat || '}')   mapPoint 

              from zhdb_ods.ODS_HJZL_KM_CARLINEPOINT points  

                    where points.LINEID = LINE.LINEID 
                   and Lng is not null and Lat is not null 
                   group by  lineID 

               ) mapPoints 
               from zhdb_ods.ODS_HJZL_KM_CARLINE line 
          left join  zhdb_ods.ODS_HJZL_KM_CARAREA area  on line.AreaID = area.AREAID  ) orig

效果:

方法二

SQL:

select LINEID, AREAID, LINENAME, CARCODE, CARSIM, LINENOTES, LINESORTINDEX, AREANAME, AREASORTINDEX,  '[' || MAPPOINTS || ']' as MAPPOINTS from(  
          select   LINEID, LINE.AREAID, LINENAME, CARCODE, CARSIM, LINENOTES, LINESORTINDEX, areaname, areasortindex, ( 
          select XMLAGG(XMLELEMENT(E, '{ \"LPOINTSORTINDEX\":' || LPOINTSORTINDEX || '  ,\"LPOINTNAMES\": ' || to_char(LPOINTNAMES) || ',\"lpointid\": ' || lpointid || ',\"lng\":' || lng || ',\"Lat\":' || Lat || '}')).EXTRACT('//text()').getclobval() codes from zhdb_ods.ODS_HJZL_KM_CARLINEPOINT points  


                    where points.LINEID = LINE.LINEID 
                   and Lng is not null and Lat is not null 
                   group by  lineID 

               ) mapPoints 
               from zhdb_ods.ODS_HJZL_KM_CARLINE line 
          left join  zhdb_ods.ODS_HJZL_KM_CARAREA area  on line.AreaID = area.AREAID  ) orig

效果: