oracle数据库sql语句 通过分隔符求一个字段里的字符个数

问题描述:

通过分隔符求一个字段里的字符个数,例如,求id为1的type里面省份的个数;

 

 

 sql语句如下:

SELECT COUNT(N.VALUE)
  FROM (SELECT M.NAME NAME, COUNT(NAME) VALUE
          FROM (SELECT REGEXP_SUBSTR(TS.type, '[^|]+', 1, L) AS NAME
                  FROM test_columns TS,
                       (SELECT LEVEL L FROM DUAL CONNECT BY LEVEL <= 1000)
                 WHERE L(+) <=
                       LENGTH(TS.type) - LENGTH(REPLACE(TS.type, '|')) + 1
                   and ts.id = '1') M
         WHERE NAME IS NOT NULL
         GROUP BY (M.NAME)
         ORDER BY COUNT(1) DESC) N

结果:

 

 

 

剖析sql语句:

SELECT COUNT(N.VALUE)
  FROM (SELECT M.NAME NAME, COUNT(NAME) VALUE
          FROM (SELECT REGEXP_SUBSTR(TS.type, '[^|]+', 1, L) AS NAME    -->固定函数,REGEXP_SUBSTR() 函数中的 ‘[^|]+’ 是根据你的字段是以什么隔开的;
                  FROM test_columns TS,
                       (SELECT LEVEL L FROM DUAL CONNECT BY LEVEL <= 1000)
                 WHERE L(+) <=
                       LENGTH(TS.type) - LENGTH(REPLACE(TS.type, '|')) + 1   -->WHERE条件中的(+)必须加上、否则 TS.NAME 字段为空的数据无法取得、而且加上(+)后、SQL执行效率能够提高几个数量级
但是这里由于我们需要筛选出TS.NAME字段为空的数据,所以后面加了NAME IS NOT NULL
and ts.id = '1') M WHERE NAME IS NOT NULL GROUP BY (M.NAME) ORDER BY COUNT(1) DESC) N

 

 

 错误做法:

  

 

posted @ 2021-06-28 00:01  等你的夏天  阅读(775)  评论(0编辑  收藏  举报