oracle 查询结果用逗号隔开（非存储过程）

最新推荐文章于 2024-08-12 16:38:37 发布

gjldwz

最新推荐文章于 2024-08-12 16:38:37 发布

阅读量1.6k

点赞数

分类专栏： Oracle_大类

本文链接：https://blog.csdn.net/gjldwz/article/details/17422235

版权

Oracle_大类专栏收录该内容

6 篇文章 0 订阅

订阅专栏

1.死的

  SELECT trim(REGEXP_SUBSTR (replace('aa,bb,cc',''), '[^,]+', 1,ROWNUM)) as keywords
            from dual t
            CONNECT BY ROWNUM <=length(regexp_replace('aa,bb,cc','[^,]',''))+1
                              --LENGTH ('aa,bb,cc')-LENGTH (REPLACE ('aa,bb,cc',','))+1  和上面一样

2.活的俩种方法

(1).

            SELECT  distinct trim(REGEXP_SUBSTR(replace(ttt.keywords,' ',','), '[^,]+', 1,ttt.lv)) as keywords
             from (
                 select *
                 from (select t.keywords,nvl(length(regexp_replace(replace(t.keywords, ' ', ','),'[^,]','')),0) + 1 cnt
                        from (select distinct keywords from TEMP_TBL_KB_VIDEO_KEYWORD where keywords  IS NOT NULL) t) tt,
                     (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 100) cn
               where cn.lv <= tt.cnt
            ) ttt

(2).

      SELECT VIDEOID AS VIDEO_ID, KEYWORD AS KEYWORD
        FROM (WITH VKD AS (SELECT VIDEOID,
                                  REPLACE(TRIM(KEYWORDS), ' ', ',') AS KEYWORDS
                             FROM TEMP_TBL_KB_VIDEO_KEYWORD VVI_A
                            WHERE KEYWORDS IS NOT NULL
                           
                           )
               SELECT VIDEOID,
                      SUBSTR(T.KEYWORDS_LOOP,
                             INSTR(T.KEYWORDS_LOOP, ',', 1, C.LV) + 1,
                             INSTR(T.KEYWORDS_LOOP, ',', 1, C.LV + 1) -
                             (instr(T.KEYWORDS_LOOP, ',', 1, C.LV) + 1)) AS KEYWORD
                 FROM (SELECT VIDEOID,
                              ',' || KEYWORDS || ',' AS KEYWORDS_LOOP,
                              LENGTH(KEYWORDS || ',') -
                              NVL(LENGTH(REPLACE(KEYWORDS, ',')), 0) AS CNT
                         FROM VKD) T,
                      (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 100) C
                WHERE C.LV <= T.CNT)
                WHERE KEYWORD IS NOT NULL