sql语句,字段拆分并统计次数

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