有点难度的sql语句,字段拆分并统计次数

[fanhui1022]

我有一个表A（举个例子，求出都有几个人吃了苹果和梨）:
name str
张三 梨
李四 苹果
王五 梨;苹果

这样一个表如何能在group by str之后实现如下结果：

str count
梨 2
苹果 2

[中国风]

參照方法，拆分再 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  

[fanhui1022]

你还真神速啊，先试试

[中国风]

with Tab  
as  
(SELECT N'a,b,c' as Col2  from dual union all  
select N'd,e' from dual union all  
select N'f'   from dual )  
SELECT   
    REGEXP_SUBSTR(Col2,'[^;]+',1,lev) AS NAME,COUNT(*) AS con  
FROM Tab,  
    (SELECT LEVEL lev FROM dual CONNECT BY LEVEL <= 100) b  
WHERE (LENGTH(Col2)-LENGTH(REPLACE(Col2,';','')))+1 >=lev  
GROUP BY REGEXP_SUBSTR(Col2,'[^;]+',1,lev) 

[dawugui]

如果只有一个;用instr即可.

[007-x]

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

[中国风]

[Quote=引用 2 楼 fanhui1022 的回复:]

你还真神速啊，先试试
[/Quote]

你改一下分隔符和列名就行了

加上一個 group by

[hupeng213]

create table t_test as
select '张三' as name, '苹果' as str from dual
union
select '张四', '梨' from dual
union
select '王五', '苹果,梨' from dual


select * from t_test

select '苹果' as str, sum(apple) as counted
from (select name,
case
when instr(str, '苹果') > 0 then
1
else
0
end as apple,
case
when instr(str, '梨') > 0 then
1
else
0
end as pear
from t_test)
union
select '梨' as str, sum(pear) as counted
from (select name,
case
when instr(str, '苹果') > 0 then
1
else
0
end as apple,
case
when instr(str, '梨') > 0 then
1
else
0
end as pear
from t_test)
嘿嘿，完全是为了解题。

[dawugui]

select str , count(1) count from
(
select name , substr(str , 1 , instr(str,';') - 1) str from tb where instr(str,';') > 0 
union all
select name , substr(str , instr(str,';') + 1 , len(str)) str from tb where instr(str,';') > 0 
union all
select name ,  str from tb where instr(str,';') = 0 
) t
group by str

[hudingchen]

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

[fanhui1022]

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  

看的有点晕，没太理解是怎么实现的，能否解释一下实现步骤

[中国风]

看的有点晕，没太理解是怎么实现的，能否解释一下实现步骤
----------
instr--判斷分隔符位置
substr--取第幾位到幾位

[fanhui1022]

行，结贴，谢谢各位!