请教一个SQL语句，看不用存储过程能做到吗？

Question

如表中有下面列
id                   content
--------------------------------------------
1                   "西瓜"
2                   "桃"
3                   "菠萝；西瓜"
4                   "西瓜；荔枝；苹果；李子"
5                   "苹果"
6                   "李子"
7                   "李子"
8                   "荔枝"
然后想做的是：
把出现的水果，按出现次数顺序排列出来。
有好办法吗？如果不用存储过程？

Answer 1

10g的结果，感觉用过程好点
with t as (select 1,'西瓜' "A" from dual union all 
select 2,'桃' from dual union all
select 3,'菠萝;西瓜' from dual union all
select 4,'西瓜;荔枝;苹果;李子' from dual union all
select 5,'苹果' from dual union all
select 6,'李子' from dual union all
select 7,'李子' from dual union all
select 8,'荔枝' from dual 
)
select c1,count(*) from (
select  c1 from 
 (
SELECT     
       rtrim(regexp_substr(A || ';', '.*?' || ';', 1, 1), ';') AS c1,
       rtrim(regexp_substr(A || ';', '.*?' || ';', 1, 2), ';') AS c2,
       rtrim(regexp_substr(A || ';', '.*?' || ';', 1, 3), ';') AS c3,
 rtrim(regexp_substr(A || ';', '.*?' || ';', 1, 4), ';') AS c4
  FROM t) union all 
select  c2 from 
 (
SELECT     
       rtrim(regexp_substr(A || ';', '.*?' || ';', 1, 1), ';') AS c1,
       rtrim(regexp_substr(A || ';', '.*?' || ';', 1, 2), ';') AS c2,
       rtrim(regexp_substr(A || ';', '.*?' || ';', 1, 3), ';') AS c3,
 rtrim(regexp_substr(A || ';', '.*?' || ';', 1, 4), ';') AS c4
  FROM t)
union all 
select  c3 from 
 (
SELECT     
       rtrim(regexp_substr(A || ';', '.*?' || ';', 1, 1), ';') AS c1,
       rtrim(regexp_substr(A || ';', '.*?' || ';', 1, 2), ';') AS c2,
       rtrim(regexp_substr(A || ';', '.*?' || ';', 1, 3), ';') AS c3,
 rtrim(regexp_substr(A || ';', '.*?' || ';', 1, 4), ';') AS c4
  FROM t)
union all 
select  c4 from 
 (
SELECT     
       rtrim(regexp_substr(A || ';', '.*?' || ';', 1, 1), ';') AS c1,
       rtrim(regexp_substr(A || ';', '.*?' || ';', 1, 2), ';') AS c2,
       rtrim(regexp_substr(A || ';', '.*?' || ';', 1, 3), ';') AS c3,
 rtrim(regexp_substr(A || ';', '.*?' || ';', 1, 4), ';') AS c4
  FROM t) ) where c1 is not null group by c1 order by count(*) desc
;

Answer 2

with t as (select 1,'西瓜' "A" from dual union all 
select 2,'桃' from dual union all
select 3,'菠萝;西瓜' from dual union all
select 4,'西瓜;荔枝;苹果;李子' from dual union all
select 5,'苹果' from dual union all
select 6,'李子' from dual union all
select 7,'李子' from dual union all
select 8,'荔枝' from dual 
)
select wm_concat(replace(A,';',',')) from t
这个的结果是   西瓜,桃,菠萝,西瓜,西瓜,荔枝,苹果,李子,苹果,李子,李子,荔枝
除非你定义一个拆分转换函数，否则这个语句就太复杂了 

Answer 3

with t as (select 1 ID ,'西瓜' "A" from dual union all  
select 2 ID,'桃' from dual union all
select 3 ID,'菠萝;西瓜' from dual union all
select 4 ID,'西瓜;荔枝;苹果;李子' from dual union all
select 5 ID,'苹果' from dual union all
select 6 ID,'李子' from dual union all
select 7 ID,'李子' from dual union all
select 8 ID,'荔枝' from dual  
)
SELECT NAME,COUNT(*)   FROM (
SELECT ID,substr(';'||t.a||';',decode(instr(';'||t.a||';',';',1,a.b),0,0,instr(';'||t.a||';',';',1,a.b)+1),
decode(instr(';'||t.a||';',';',1,a.b+1),0,0,instr(';'||t.a||';',';',1,a.b+1)-instr(';'||t.a||';',';',1,a.b)-1)) NAME   FROM t ,
(SELECT ROWNUM b FROM dual CONNECT BY ROWNUM<=(SELECT MAX(length((translate(';'||t.a||';',';'||t.a,';')))) FROM t )) a
)
WHERE NAME IS NOT NULL
GROUP BY NAME
ORDER BY COUNT(*) DESC;
试试这个应该能满足你的要求

Answer 4

上面sql的思路实际比较简单，先根据表中含水果数量最多的那条数据以递归查询的形式产生一个选择器，
以该选择器与水果表做笛卡尔关联，截取其中的水果名称，将水果表转成一个单值表，转换的过程会产生值为null的数据，所以要滤除一下，然后对但只表分组就可以得到最后结果。

Answer 5

做了个试验，可以建立一个type和一个funtion，用于把content转换成以“，”为标志的列，当然可以换做“；”，然后再进行分类汇总。
tpye和funtion可以参看http://dev.firnow.com/course/7_databases/oracle/oraclexl/20090304/158057.html 要把单引号改成英文。具体过程如下：create table t2
( id  number,
  content varchar2(20)   
);
insert into t2 values( 1, '西瓜');
insert into t2 values( 2, '桃');
insert into t2 values( 3, '菠萝,西瓜');
insert into t2 values( 4, '西瓜,荔枝,苹果,李子');
insert into t2 values( 5, '苹果');
insert into t2 values( 6, '李子');
insert into t2 values( 7, '李子');
insert into t2 values( 8, '荔枝');
select * from t2;
Oracle函数 实现 Split
第一，先创建一个Type    
CREATE OR REPLACE TYPE type_split IS TABLE OF VARCHAR2 (4000); 
第二，创建函数 
create or replace function split(p_list varchar2,p_sep varchar2 := ',') return type_split pipelined 
IS 
 l_idx pls_integer; 
 v_list varchar2(50) := p_list; 
 begin 
      loop 
           l_idx := instr(v_list,p_sep); 
           if l_idx > 0 then 
               pipe row(substr(v_list,1,l_idx-1)); 
               v_list := substr(v_list,l_idx+length(p_sep)); 
           else 
                pipe row(v_list); 
                exit; 
           end if; 
      end loop;       return; 
 end split; 
第三，调试 
select * from table(split('aaa,bbb,ccc',','));--下面是得到你要的结果
select b.column_value,count(*) cnt
  from t2,table(split(t2.content)) b
 group by b.column_value
 order by count(*);

Answer 6

create table fruit(
  id NUMBER(18,0),
  content VARCHAR2(4000)
);INSERT INTO fruit(id, content) values(1,'"西瓜"');
INSERT INTO fruit(id, content) values(2,'"桃"');
INSERT INTO fruit(id, content) values(3,'"菠萝；西瓜"');
INSERT INTO fruit(id, content) values(4,'"西瓜；荔枝；苹果；李子"');
INSERT INTO fruit(id, content) values(5,'"苹果"');
INSERT INTO fruit(id, content) values(6,'"李子"');
INSERT INTO fruit(id, content) values(7,'"李子"');
INSERT INTO fruit(id, content) values(8,'"荔枝"');COMMIT;select * from fruit;CREATE GLOBAL TEMPORARY TABLE mem_fruit
(
  fruit_name VARCHAR2(4000),
  times NUMBER(18,0)
)
ON COMMIT DELETE ROWS;CREATE OR REPLACE PROCEDURE fruit_count_proc(i_fruit_name VARCHAR2, o_cur OUT SYS_REFCURSOR)
IS
  sqlstr VARCHAR2(4000);
  v_fruit_name VARCHAR2(1000);
  v_content VARCHAR2(4000);
  v_loop NUMBER(18,0);
  v_split VARCHAR2(20);
  CURSOR c_ur1 IS SELECT a.content FROM fruit a WHERE instr(a.content,'；')>0;
  CURSOR c_ur2 IS SELECT a.content FROM fruit a WHERE instr(a.content,'；')>0 AND ( content LIKE '"'||i_fruit_name||'；'||'%' OR  content LIKE '%'||'；'||i_fruit_name||'"' OR content LIKE '%'||'；'||i_fruit_name||'；'||'%' );
BEGIN
  IF i_fruit_name IS NULL THEN  -- 如果传入的水果名称为空，统计所有水果出现的次数
    -- 先汇总一行记录中只出现单个水果名称的记录行(假设你当有多个水果名称时，名称之间以“；”字符分隔
    sqlstr := 'INSERT INTO mem_fruit SELECT a.content fruit_name, count(1) as times FROM fruit a WHERE instr(a.content,''；'')=0 GROUP BY a.content';
    EXECUTE IMMEDIATE sqlstr;
    -- dbms_output.put_line(sqlstr);    FOR r_ur in c_ur1 LOOP
      v_content :='；'||replace(r_ur.content,'"','')||'；';
      dbms_output.put_line('v_content: '||v_content);
      v_loop := length(v_content)-length(replace(v_content,'；',''));
      FOR i in 2 .. v_loop LOOP
        v_split := substr(v_content, instr(v_content,'；',1,i-1)+1, instr(v_content,'；',1,i)-instr(v_content,'；',1,i-1)-1);
        v_split := '"'||v_split||'"';
        dbms_output.put_line('v_split: '||v_split);
        INSERT INTO mem_fruit(fruit_name,times) VALUES(v_split,1);
      END LOOP;
    END LOOP;  ELSE
    v_fruit_name := '"'||i_fruit_name||'"';
    sqlstr := 'INSERT INTO mem_fruit SELECT a.content fruit_name, count(1) as times FROM fruit a WHERE instr(a.content,''；'')=0 AND content=:v_fruit_name GROUP BY a.content';
    EXECUTE IMMEDIATE sqlstr USING v_fruit_name;    -- DECLARE CURSOR c_ur IS SELECT a.content FROM fruit a WHERE instr(a.content,'；')>0 AND ( content LIKE '"'||i_fruit_name||'；'||'%' OR  content LIKE '%'||'；'||i_fruit_name||'"' OR content LIKE '%'||'；'||i_fruit_name||'；'||'%' );
    FOR r_ur in c_ur2 LOOP
      dbms_output.put_line('luoyoumou');
      v_content :='；'||replace(r_ur.content,'"','')||'；';
      v_loop := length(v_content)-length(replace(v_content,'；',''));
      FOR i in 2 .. v_loop LOOP
        v_split := substr(v_content, instr(v_content,'；',1,i-1)+1, instr(v_content,'；',1,i)-instr(v_content,'；',1,i-1)-1);
        v_split := '"'||v_split||'"';
        INSERT INTO mem_fruit(fruit_name,times) VALUES(v_split,1);
      END LOOP;
    END LOOP;
  END IF;
  sqlstr := 'SELECT fruit_name, sum(times) as cnt FROM mem_fruit WHERE ( fruit_name=:v_fruit_name OR :i_fruit_name IS NULL) GROUP BY fruit_name ORDER BY sum(times) desc';
  -- sqlstr := 'SELECT * FROM mem_fruit';
  OPEN o_cur FOR sqlstr USING v_fruit_name, i_fruit_name;
END;
/set serveroutput on;
var c_cur refcursor;
exec fruit_count_proc('李子',:c_cur);
print c_cur;set serveroutput on;
var c_cur refcursor;
exec fruit_count_proc('',:c_cur);
print c_cur;

Answer 7

create table fruit(
  id NUMBER(18,0),
  content VARCHAR2(4000)
);INSERT INTO fruit(id, content) values(1,'"西瓜"');
INSERT INTO fruit(id, content) values(2,'"桃"');
INSERT INTO fruit(id, content) values(3,'"菠萝；西瓜"');
INSERT INTO fruit(id, content) values(4,'"西瓜；荔枝；苹果；李子"');
INSERT INTO fruit(id, content) values(5,'"苹果"');
INSERT INTO fruit(id, content) values(6,'"李子"');
INSERT INTO fruit(id, content) values(7,'"李子"');
INSERT INTO fruit(id, content) values(8,'"荔枝"');COMMIT;select * from fruit;CREATE GLOBAL TEMPORARY TABLE mem_fruit
(
  fruit_name VARCHAR2(4000),
  times NUMBER(18,0)
)
ON COMMIT DELETE ROWS;CREATE OR REPLACE PROCEDURE fruit_count_proc(i_fruit_name VARCHAR2, o_cur OUT SYS_REFCURSOR)
IS
  sqlstr VARCHAR2(4000);
  v_fruit_name VARCHAR2(1000);
  v_content VARCHAR2(4000);
  v_loop NUMBER(18,0);
  v_split VARCHAR2(20);
  CURSOR c_ur1 IS SELECT a.content FROM fruit a WHERE instr(a.content,'；')>0;
  CURSOR c_ur2 IS SELECT a.content FROM fruit a WHERE instr(a.content,'；')>0 AND ( content LIKE '"'||i_fruit_name||'；'||'%' OR  content LIKE '%'||'；'||i_fruit_name||'"' OR content LIKE '%'||'；'||i_fruit_name||'；'||'%' );
BEGIN
  IF i_fruit_name IS NULL THEN  -- 如果传入的水果名称为空，统计所有水果出现的次数
    -- 先汇总一行记录中只出现单个水果名称的记录行(假设你当有多个水果名称时，名称之间以“；”字符分隔
    sqlstr := 'INSERT INTO mem_fruit SELECT a.content fruit_name, count(1) as times FROM fruit a WHERE instr(a.content,''；'')=0 GROUP BY a.content';
    EXECUTE IMMEDIATE sqlstr;    FOR r_ur in c_ur1 LOOP
      v_content :='；'||replace(r_ur.content,'"','')||'；';
      v_loop := length(v_content)-length(replace(v_content,'；',''));
      FOR i in 2 .. v_loop LOOP
        v_split := substr(v_content, instr(v_content,'；',1,i-1)+1, instr(v_content,'；',1,i)-instr(v_content,'；',1,i-1)-1);
        v_split := '"'||v_split||'"';
        INSERT INTO mem_fruit(fruit_name,times) VALUES(v_split,1);
      END LOOP;
    END LOOP;  ELSE
    v_fruit_name := '"'||i_fruit_name||'"';
    sqlstr := 'INSERT INTO mem_fruit SELECT a.content fruit_name, count(1) as times FROM fruit a WHERE instr(a.content,''；'')=0 AND content=:v_fruit_name GROUP BY a.content';
    EXECUTE IMMEDIATE sqlstr USING v_fruit_name;    FOR r_ur in c_ur2 LOOP
      v_content :='；'||replace(r_ur.content,'"','')||'；';
      v_loop := length(v_content)-length(replace(v_content,'；',''));
      FOR i in 2 .. v_loop LOOP
        v_split := substr(v_content, instr(v_content,'；',1,i-1)+1, instr(v_content,'；',1,i)-instr(v_content,'；',1,i-1)-1);
        v_split := '"'||v_split||'"';
        INSERT INTO mem_fruit(fruit_name,times) VALUES(v_split,1);
      END LOOP;
    END LOOP;
  END IF;  sqlstr := 'SELECT fruit_name, sum(times) as cnt FROM mem_fruit WHERE ( fruit_name=:v_fruit_name OR :i_fruit_name IS NULL) GROUP BY fruit_name ORDER BY sum(times) desc';
  OPEN o_cur FOR sqlstr USING v_fruit_name, i_fruit_name;
END;
/set serveroutput on;
var c_cur refcursor;
exec fruit_count_proc('李子',:c_cur);
print c_cur;set serveroutput on;
var c_cur refcursor;
exec fruit_count_proc('',:c_cur);
print c_cur;------------------------------------------------
scott@SZTYORA> set serveroutput on;
scott@SZTYORA> var c_cur refcursor;
scott@SZTYORA> exec fruit_count_proc('',:c_cur);PL/SQL 过程已成功完成。已用时间:  00: 00: 00.00
scott@SZTYORA> print c_cur;FRUIT_NAME                                      CNT
---------------------------------------- ----------
"李子"                                            3
"西瓜"                                            3
"苹果"                                            2
"荔枝"                                            2
"菠萝"                                            1
"桃"                                              1已选择6行。

Answer 8

15:25:38 system@PRACTICE> WITH tb AS (
15:25:48   2  SELECT 1 id,'西瓜' content FROM dual
15:25:48   3  UNION ALL
15:25:48   4  SELECT 2,'桃' FROM dual
15:25:48   5  UNION ALL
15:25:48   6  SELECT 3,'菠萝;西瓜' FROM dual
15:25:48   7  UNION ALL
15:25:48   8  SELECT 4,'西瓜;荔枝;苹果;李子' FROM dual
15:25:48   9  UNION ALL
15:25:48  10  SELECT 5,'苹果' FROM dual
15:25:48  11  UNION ALL
15:25:48  12  SELECT 6,'李子' FROM dual
15:25:48  13  UNION ALL
15:25:48  14  SELECT 7,'李子' FROM dual
15:25:48  15  UNION ALL
15:25:48  16  SELECT 8,'荔枝' FROM dual
15:25:48  17  )
15:25:48  18  SELECT COUNT(1) num,content FROM (
15:25:48  19  SELECT SUBSTR(content, DECODE(ROWNUM,1,1,INSTR(content,';',1,ROWNUM-1)+1), INSTR(content,';',1,ROWNUM)-DECODE(ROWNUM,1,1,INSTR(content,';',1,ROWNUM-1)+1)) content
15:25:48  20  FROM (SELECT REPLACE(WM_CONCAT(content),',',';')||';' content FROM tb)
15:25:48  21  CONNECT BY INSTR(content,';',1,ROWNUM) > 0
15:25:48  22  )
15:25:48  23  GROUP BY content ORDER BY COUNT(1) DESC;       NUM CONTENT
---------- ---------------
         3 西瓜
         3 李子
         2 荔枝
         2 苹果
         1 桃
         1 菠萝已选择6行。已用时间:  00: 00: 00.01

Answer 9

来个另类点的:
with fruit as (select 1 AS ID,'西瓜' names from dual union all  
select 2,'桃' from dual union all
select 3,'菠萝;西瓜' from dual union all
select 4,'西瓜;荔枝;苹果;李子' from dual union all
select 5,'苹果' from dual union all
select 6,'李子' from dual union all
select 7,'李子' from dual union all
select 8,'荔枝' from dual  
)
SELECT 
column_value,
COUNT(1)
FROM
(
SELECT id
      ,column_value
  FROM (SELECT id,';'||names||';' names FROM fruit) t1     ----- 前后拼上逗号是为了下面定位拆分
      ,TABLE(CAST(MULTISET( SELECT SUBSTR (names            ----- 此处竟然可以看到t1.names
                                          ,INSTR (names, ';', 1, LEVEL  ) + 1
                                          ,INSTR (names, ';', 1, LEVEL+1) - INSTR (names, ';', 1, LEVEL) -1 
                                          ) 
                              FROM DUAL
                            CONNECT BY LEVEL <= LENGTH(names)-LENGTH(REPLACE(names,';',''))-1 
                           )
             AS SYS.ODCIVARCHAR2LIST ) ------ SYS.ODCIVARCHAR2LIST 可以换成任意一个TABLE OF VARCHAR2的嵌套表类型
             ) t2
)
GROUP BY column_value;

Answer 10

哦,忘了加个order by 1在底下