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
错误做法: