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

Question

我有一个表A（举个例子，求出都有几个人吃了苹果和梨）:
name         str
张三         梨
李四         苹果
王五         梨;苹果这样一个表如何能在group by str之后实现如下结果：str          count
梨           2
苹果         2

Answer 1

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

Answer 2

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) 

Answer 3

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

Answer 4

你改一下分隔符和列名就行了加上一個 group by 

Answer 5

create table t_test as 
select '张三' as name, '苹果' as str from dual
union 
select '张四', '梨' from dual
union
select '王五', '苹果,梨'  from dual
select * from t_testselect '苹果' 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)
嘿嘿，完全是为了解题。

Answer 6

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

Answer 7

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

Answer 8

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  
看的有点晕，没太理解是怎么实现的，能否解释一下实现步骤

Answer 9

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