SYS_CONNECT_BY_PATH 函数综合应用应用多字段拼在一行字符串

最新推荐文章于 2021-02-26 23:48:24 发布

小IT人

最新推荐文章于 2021-02-26 23:48:24 发布

阅读量1.4k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： ORACLE

本文链接：https://blog.youkuaiyun.com/weixin_38466660/article/details/78739471

ORACLE 专栏收录该内容

0 篇文章

订阅专栏

本文详细介绍了如何使用SYS_CONNECT_BY_PATH函数将多个字段的数据拼接成一行字符串，结合实例展示了其在数据处理中的具体应用，尤其是在构建树状结构时的关键作用。

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

SELECT
   f.nowdate AS "nowdate",
   f.DAYOFWEEK "dayofweek",
   f.WEEKOFMONTH AS "weekofmonth",
   f.YEARMONTH "yearmonth",
   COUNT (1) AS "messageNum",
   LTRIM (
       MAX (
           SYS_CONNECT_BY_PATH (f.C2 || ',' || f.state, '|') -----------SYS_CONNECT_BY_PATH函数主要作用是可以把一个父节点下的所有子节点通过某个字符进行区分，然后连接在一个列中显示。
       ) KEEP (
           DENSE_RANK LAST -------------DENSE_RANK是排序策略 FIRST/LAST就是对数据进行筛选了
           ORDER BY
               f.pnum
       ),
       '|'

) AS "message"

----------------------------------------------

FROM
   (
       SELECT
           A .nowdate,
           A .yearmonth,
           A .weekofmonth,
           A .dayofweek,
           A .state,
           b.c2,
           ROW_NUMBER () OVER (
               PARTITION BY A .nowdate
               ORDER BY
                   b.c2
           ) AS pnum,       ----------------------------按照日期进行生成序号排序（生产序号的方法通过over()函数里面的语句来控制。

           ROW_NUMBER () OVER (
               PARTITION BY A .nowdate
               ORDER BY
                   b.c2
           ) - 1 AS lnum
       FROM
           skin_serve A
       LEFT JOIN SYS_COMPANY B ON A .SURVERUNIT = b. ID
       WHERE
           A .del_flag = '0'
       AND A .yearmonth = '201711'
   ) f
GROUP BY
   f.DAYOFWEEK,
   f.WEEKOFMONTH,
   f.NOWDATE,
   f.YEARMONTH CONNECT BY f.lnum = PRIOR f.pnum

AND f.nowdate = PRIOR f.nowdate START WITH f.pnum = 1 -----------------根据排序建树状结构

-------------------------------------------------------------------------------------