sql函数学习

今天收到一个新需求，将系统内共享组的作业人员按照一级共享分中心为纬度进行进行去重统计人数，结合chatgpt进行sql编写，并学习新知识点。

LISTAGG(DISTINCT …, ‘、’) WITHIN GROUP(ORDER BY …) : 用于将多个值连接成一个字符串，并指定连接符（这里是’、’）。WITHIN GROUP(ORDER BY ...)部分允许你指定连接时的排序顺序。在这里用于将用户名连接成一个字符串 (b_values_concat)；

REGEXP_SUBSTR(…) : 正则表达式子字符串提取函数。它提取与正则表达式匹配的子字符串。在这里用于从SYS_CONNECT_BY_PATH生成的路径中提取不同层级的名称，比如“共享分中心一级”、“二级”等。（还未掌握）

SYS_CONNECT_BY_PATH(…) : 生成一个层级路径字符串。SYS_CONNECT_BY_PATH (column, delimiter)会为每一行生成从根节点到当前行的路径，用指定的分隔符连接。在这里用于生成full_path，以及提取层级名称（还未掌握）；

CONNECT BY … : 这是一个用于递归查询的子句，用来查询层级数据。在这段SQL中，CONNECT BY PRIOR pk_workinggroup = parentid用于递归地查找层级结构（还未掌握）；

START WITH … : 用于指定递归查询的起点。在这里START WITH parentid = '~'指定了根节点；

ORDER BY … : 排序函数。在这里用于对最终结果按“共享分中心一级”排序；

LEVEL : Oracle的伪列，表示当前行在层次结构中的层级。在这段SQL中，LEVEL用于标识各层级的位置；

--sql语句
SELECT a.共享分中心一级,
       COUNT(DISTINCT b.user_name) AS user_count, -- 统计人数
       LISTAGG(DISTINCT b.user_name, '、') WITHIN GROUP(ORDER BY b.user_name) AS b_values_concat
  FROM (SELECT pk_workinggroup,
               code,
               name,
               LEVEL AS hierarchy_level, --级别
               REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(name, '/'), '[^/]+', 1, 1) AS 共享分中心一级,
               REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(name, '/'), '[^/]+', 1, 2) AS 二级,
               REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(name, '/'), '[^/]+', 1, 3) AS 三级,
               REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(name, '/'), '[^/]+', 1, 4) AS 四级,
               SYS_CONNECT_BY_PATH(name, ' / ') AS full_path
          FROM ssctp_workinggroup
       --   where enablestate = '2'
         START WITH parentid = '~'
        CONNECT BY PRIOR pk_workinggroup = parentid
         ORDER BY full_path) a
  LEFT JOIN (SELECT b.user_name, a.pk_workinggroup
               FROM ssctp_workinggroup_user a
               LEFT JOIN sm_user b
                 ON b.cuserid = a.pk_user
             ) b
    ON a.pk_workinggroup = b.pk_workinggroup
 GROUP BY a.共享分中心一级
 ORDER BY a.共享分中心一级;