我有一个表A（举个例子，求出都有几个人吃了苹果和梨）:
name str
张三 梨
李四 苹果
王五 梨;苹果
这样一个表如何能在group by str之后实现如下结果：
str count
梨 2
苹果 2
------解决方案--------------------
- SQL code
參照方法，拆分再 group by /**方法1**/ with Tab as (select 1 as Col1,N'a,b,c' as Col2 from dual union all select 2,N'd,e' from dual union all select 3,N'f' from dual ) SELECT Col1,substr(Col2,lev,instr(Col2||',',',',lev)-lev) as Col2 from Tab ,(SELECT LEVEL lev FROM DUAL CONNECT BY LEVEL<=100) WHERE substr(','||Col2,lev,1)=',' /** 条件可换为 instr(','||Col2,',',lev)=lev**/ order by Col1 /**方法2 REGEXP_SUBSTR(srcstr, pattern, position, occurrence, modifier) __srcstr ：检索字符串 __pattern ：匹配模式 __position ：搜索srcstr的起始位置（默认为1） __occurrence：搜索第几次出现匹配模式的字符串（默认为1） __modifier ：检索模式（'i'不区分大小写进行检索；'c'区分大小写进行检索。默认为'c'。） **/ with Tab as (select 1 as Col1,N'a,b,c' as Col2 from dual union all select 2,N'd,e' from dual union all select 3,N'f' from dual ) SELECT Col1,REGEXP_SUBSTR(Col2,'[^,]+',1,lev) FROM Tab, (SELECT LEVEL lev FROM dual CONNECT BY LEVEL <= 100) b WHERE (LENGTH(Col2)-LENGTH(REPLACE(Col2,',','')))+1 >=lev ORDER BY Col1,lev
------解决方案--------------------
- SQL code
WITH t1 AS (SELECT '张三' NAME, '梨' str FROM DUAL UNION ALL SELECT '李四', '苹果' FROM DUAL UNION ALL SELECT '王五', '梨;苹果' FROM DUAL)SELECT str, COUNT (str) num FROM (SELECT REGEXP_SUBSTR (a.str, '[^;]+', 1, b.l) str FROM t1 a, (SELECT LEVEL l FROM DUAL CONNECT BY LEVEL <= 10) b WHERE REGEXP_SUBSTR (a.str, '[^;]+', 1, b.l) IS NOT NULL)GROUP BY str
------解决方案--------------------
- SQL code
SQL> WITH t AS ( 2 SELECT '1' tid,'apple,pear,banana' fruit FROM DUAL UNION ALL 3 SELECT '2' tid,'apple,banana' fruit FROM DUAL UNION ALL 4 SELECT '3' tid,'apple,pear' fruit FROM DUAL UNION ALL 5 SELECT '4' tid,'banana' fruit FROM DUAL 6 ) 7 SELECT m.fruit, 8 COUNT(*) num 9 FROM (SELECT tid, 10 RTRIM(REGEXP_SUBSTR(fruit || ',', '.*?' || ',', 1, LEVEL), ',') AS fruit 11 FROM t 12 CONNECT BY tid = CONNECT_BY_ROOT(tid) 13 AND LEVEL <= 14 LENGTH(REGEXP_REPLACE(fruit || ',', '[^' || ',' || ']', ''))) m 15 GROUP BY m.fruit 16 ORDER BY m.fruit 17 ;FRUIT NUM------------------------------------ ----------apple 3banana 3pear 2