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 3
banana 3
pear 2
原文地址:http://topic.csdn.net/u/20111122/15/14794b37-9114-4c8f-9239-f31f264b5ead.html