sql问题 求大神帮助!!

[lixiaohei4527]

IP
10.79.1.1;10.79.1.4
10.79.1.2;10.79.1.3
10.79.1.4;10.79.1.1;10.79.1.2
10.79.1.1
查询结果如上 如何统计ip的次数

[lixiaohei4527]

每个ip的次数

[IvanO_O]

SELECT IP, COUNT(*) FROM TABLE GROUP BY IP

[小灰狼W]

WITH RN AS (SELECT ROWNUM N FROM DUAL CONNECT BY ROWNUM <= 10) SELECT SINGLE_IP, COUNT(1) CNT FROM (SELECT SUBSTR(A.IP, INSTR(';' || A.IP, ';', 1, B.N), INSTR(A.IP || ';', ';', 1, B.N) - INSTR(';' || A.IP, ';', 1, B.N)) SINGLE_IP FROM TAB A, RN B WHERE LENGTH(A.IP) - LENGTH(REPLACE(A.IP, ';')) + 1 >= B.N) GROUP BY SINGLE_IP; rn是构造的一个临时表，用来将ip拆分出来。里面的数字10可以按实际需要修改，要比字段中包含的ip数的最大值更大

[陈灬风]

with tmp as (select REGEXP_REPLACE(regexp_substr(id, '[^;]+', 1, LEVEL, 'i'), ',') id from (select to_char(wmsys.wm_concat(concat(t1.name, ';'))) as id from you_table) connect by level <= regexp_count(id, ';')) select id, sum(1) from tmp group by id