oracle统计字段次数,有点难度的sql语句,字段拆分并统计次数解决方案

SQL codeSQL> 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