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

来源：互联网发布：ios程序员自我介绍编辑：程序博客网时间：2024/05/16 17:01

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

0 0