wm_concat与listagg

最新推荐文章于 2022-08-22 14:24:34 发布

带刀老排长

最新推荐文章于 2022-08-22 14:24:34 发布

阅读量395

点赞数

CC 4.0 BY-SA版权

分类专栏：技术文章标签： listagg wm_concat

本文链接：https://blog.youkuaiyun.com/wjj945210759/article/details/84713404

技术专栏收录该内容

5 篇文章

订阅专栏

本文通过SQL实例，详细介绍了使用wm_concat、listagg、regexp_substr和regexp_replace等函数进行字符串操作的方法，并展示了如何利用这些函数进行数据聚合。

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

连接字符串
1.wm_concat
2.listagg

案例:
/*拆分regexp_substr 合并wm_concat 替换regexp_replace*/
WITH a AS
(SELECT '1,a,1,b,2,a,2,c,3,d,3,e,4,y' a FROM dual)
SELECT b, wm_concat(c) d
FROM (SELECT regexp_substr(a, '[^,]+', 2 * LEVEL - 1, LEVEL) b,
               regexp_substr(a, '[^,]+', 2 * LEVEL, LEVEL) c
          FROM a
        CONNECT BY LEVEL <
                   (length(a) - length(regexp_replace(a, '[^,]+', '')) + 1) / 2)
GROUP BY b;

/*拆分regexp_substr 合并listagg 替换regexp_replace*/
WITH a AS
(SELECT '1,a,1,b,2,a,2,c,3,d,3,e,4,y' a FROM dual)
SELECT b, listagg(c,',') within GROUP(ORDER BY b) d
FROM (SELECT regexp_substr(a, '[^,]+', 2 * LEVEL - 1, LEVEL) b,
               regexp_substr(a, '[^,]+', 2 * LEVEL, LEVEL) c
          FROM a
        CONNECT BY LEVEL <
                   (length(a) - length(regexp_replace(a, '[^,]+', '')) + 1) / 2)
GROUP BY b;

/*wm_concat*/
WITH a AS
(SELECT 1 id, 'a' NAME
    FROM dual
UNION ALL
SELECT 1 id, 'b' NAME
    FROM dual
UNION ALL
SELECT 1 id, 'c' NAME
    FROM dual
UNION ALL
SELECT 2 id, 'b' NAME
    FROM dual
UNION ALL
SELECT 2 id, 'a' NAME
    FROM dual
UNION ALL
SELECT 3 id, 'c' NAME
    FROM dual
UNION ALL
SELECT 3 id, 'd' NAME
    FROM dual
UNION ALL
SELECT 4 id, 'b' NAME
    FROM dual)
SELECT id, wm_concat(NAME) NAME FROM a GROUP BY id;

/*不用group by 使用over(PARTITION BY XXX)*/
WITH temp AS
(SELECT 500 population, 'China' nation, 'Guangzhou' city
    FROM dual
UNION ALL
SELECT 1500 population, 'China' nation, 'Shanghai' city
    FROM dual
UNION ALL
SELECT 500 population, 'China' nation, 'Beijing' city
    FROM dual
UNION ALL
SELECT 1000 population, 'USA' nation, 'New York' city
    FROM dual
UNION ALL
SELECT 500 population, 'USA' nation, 'Bostom' city
    FROM dual
UNION ALL
SELECT 500 population, 'Japan' nation, 'Tokyo' city
    FROM dual)
SELECT population,
       nation,
       city,
       listagg(city, ',') within GROUP(ORDER BY city) over(PARTITION BY nation) rank
FROM temp