有个麻烦的问题,请来帮助下 ,马上结贴

Question

有这样一张表A:
只有id_no字段如下:
id_no
001|002|
001|003|005|
003|006|009|010|
...
id_no是由随机个的xxx组成.最多6个XXX.另外有张表B:
id_no   num
001     10
002     13
003     17
...现在有个要求就是只要A一列的中的各个id_no在B表中的 num 之和>30才出来,
怎么弄阿 ,我说了清楚吗..........

Answer 1

select a.id_no, sum(b.num) from b,a
where instr(a.id_no,b.id_no)>0 
group by a.id_no having sum(num)>30;

Answer 2

不是很懂你的意思
如果表A中最多只能有6条数据，那么用下面的sql就可以满足你的要求.
select * from A,B where A.id_no=B.id_no 
having sum(b.num)>30;
如果表A中的数据大于6条的话，你可以随即生成2个数m，n，但要保证m<n，并且n-m<=6,n不能超过A表中数据的条数
然后就可以使用：
create view temp_A(id_no)
as
select *　from A limit m,n;
然后再使用：
select * from temp_A,B where temp_A.id_no=B.id_no 
having sum(b.num)>30;

Answer 3

楼上的 .我的A表的id_no是如这样的: 003|006|009|010|并不能直接A.id_no=B.id_no ... 可以substr(a.id_no,1,3)=b.id_no但是A的id_no是由随机个的xxx组成.最多6个XXX.  如003|006|009|010|015|  这样子
求教....

Answer 4

我想应该会用到一个oracle里面的一个对字符串扯分的函数，只是一时想不起来函数叫啥名字了扯分之后放到一个临时表里面，然后从这个临时表里面取数据就ok。但是这个函数一时忘记了，自己去实现的话，要写一个存储函数，又要测试，比较烦下班回去之后，如果还没有结贴的话，就去查资料帮你解决下吧。业务不是很难就是那个专用函数我忘记叫啥名字了！

Answer 5

create table A(id_no varchar(100))
insert into A select '001|002|'
union all select '001|003|005|' 
union all select '003|006|009|010|' create table B(id_no varchar(100),num int)
insert into B select '001',    10 
union all select '002'  ,  13 
union all select '003'  ,  17 
union all select '005'  ,  20 
select id_no from A where
exists(
select sum(num) aa from B where A.id_no  like '%'+B.id_no +'%'
having sum(num)>30
)drop table A
drop table B
Lz我不知道你要的是不是我这个意思

Answer 6

我这里有一个以前收集的别人写的函数你可以参照一下，这是从itpub上看到的:
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－自己写的一个简单的string的split函数因为有这个需求，需要切割字符串，在oracle中没有找到类似java的split函数，自己写了一个    PHP code:
    /**************************************************************************************************************************************************************
     1.判断有多少个','号，确定要查找的次数len= length(trim(translate(str, replace(str,','),' ')))，那么分割的集合元素个数是len+1
     2.循环查找','号出现的位置,用instr函数，如第1次出现位置是pos1=instr(str,',',1,1),第2次出现的位置pos2=instr(str,',',1,2),一致到第len次出现的位置instr(str,',',1,len)
     3.查找子串:substr函数.第1个子串substr(str,1,pos1-1),第2个子串substr(str,pos1+1,pos2-pos1-1),2到n-1依次类堆，到第len+1个是substr(str,pos_len+1)
     function name:str_plit
     功能：str_splite(str,pattern),第一个参数是源，第二个参数是分割的模式，返回一个集合nested table
     author:dj
     date:2008-09-17
     ****************************************************************************************************************************************************************
     **/
    create or replace type t_varray is table of varchar2(20);
    create or replace function str_split(str in varchar2,pattern in varchar2) return t_varray
     as
     len number;--分割的数组元素个数
     i number;--position位置
     res varchar2(100);
     up_len number;--上一个位置
     down_len number;--下一个位置
     cstr t_varray:=t_varray();--声明集合
     begin
       
       len:=length(trim(translate(str,replace(str,pattern),' ')));       for j in 1..len+1 loop--j是集合元素下标
           if j=1 then
           i:=instr(str,pattern,1,j);
           res:=substr(str,1,i-1);
           cstr.extend(1);
           cstr(j):=res;
           up_len:=i;
            elsif j<len+1 then
             i:=instr(str,pattern,1,j);
             down_len:=i;
             res:=substr(str,up_len+1,down_len-up_len-1);
              cstr.extend(1);
              cstr(j):=res;
             up_len:=i;
            else
             res:=substr(str,down_len+1);
              cstr.extend(1);
              cstr(j):=res;
           end if;   
       end loop;
      return cstr;
     end;
     
    --test
    declare
     str varchar2(100):='aa|bb|cc,dd,ee';
     pattern varchar2(10):='|';
     res varchar2(10);
     cstr t_varray:=t_varray();
     begin
      cstr:=str_split(str,pattern);
     for i in cstr.first .. cstr.last loop
         dbms_output.put_line(cstr(i));
     end loop;
     end;
呵呵，又来一个行转列，难道我跟行转列卯上了？听说11g中有了更简单的法子来实现行列转换，黑黑，说不得以后还得再来一篇啊。create table tmp5 (id number(2),name varchar2(10),code varchar2(50));
alter table tmp5 add constraint pk_tmp5 primary key (id);
insert into tmp5 values (1,'aaa','c001/c002/c007');
insert into tmp5 values (2,'bbb','c001/c003');
insert into tmp5 values (3,'ccc','c008/c0011/c029/c023');
insert into tmp5 values (4,'ddd','c102/c111/c112/c144/c167');select id,
name,
--rn,
--code,
substr(code, st, decode(ed, 0, length(code), ed - st)) code
from (select id,
name,
code,
rn,
decode(rn, 1, 1, instr(code, '/', 1, rn - 1) + 1) st,
instr(code, '/', 1, rn) ed
from (select *
from tmp5,
(select rownum rn
from dual
connect by rownum <=
(select max(length(code)) -
max(length(replace(code, '/', ''))) + 1
from tmp5))))
where ed > 0
or st > 1
order by id, rn;

Answer 7

想半天不知道思路对不
CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2 (4000); CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2) 
RETURN ty_str_split 
IS 
j INT := 0; 
i INT := 1; 
len INT := 0; 
len1 INT := 0; 
str VARCHAR2 (4000); 
str_split ty_str_split := ty_str_split (); 
BEGIN 
len := LENGTH (p_str); 
len1 := LENGTH (p_delimiter); WHILE j < len 
LOOP 
j := INSTR (p_str, p_delimiter, i); IF j = 0 
THEN 
j := len; 
str := SUBSTR (p_str, i); 
str_split.EXTEND; 
str_split (str_split.COUNT) := str; IF i >= len 
THEN 
EXIT; 
END IF; 
ELSE 
str := SUBSTR (p_str, i, j - i); 
i := j + len1; 
str_split.EXTEND; 
str_split (str_split.COUNT) := str; 
END IF; 
END LOOP; RETURN str_split; 
END fn_split; 
/ 测试： DECLARE 
CURSOR c 
IS 
SELECT * 
FROM TABLE (CAST (fn_split ('1;;12;;123;;1234;;12345', ';;') AS ty_str_split 
) 
); r c%ROWTYPE; 
BEGIN 
OPEN c; LOOP 
FETCH c INTO r; EXIT WHEN c%NOTFOUND; 
DBMS_OUTPUT.put_line (r.column_value); 
END LOOP; CLOSE c; 
END; 
/ 把A表变成id_no 和fn_split（id_no）两列
然后
................

Answer 8

想半天不知道思路对不
CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2 (4000); CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2) 
RETURN ty_str_split 
IS 
j INT := 0; 
i INT := 1; 
len INT := 0; 
len1 INT := 0; 
str VARCHAR2 (4000); 
str_split ty_str_split := ty_str_split (); 
BEGIN 
len := LENGTH (p_str); 
len1 := LENGTH (p_delimiter); WHILE j < len 
LOOP 
j := INSTR (p_str, p_delimiter, i); IF j = 0 
THEN 
j := len; 
str := SUBSTR (p_str, i); 
str_split.EXTEND; 
str_split (str_split.COUNT) := str; IF i >= len 
THEN 
EXIT; 
END IF; 
ELSE 
str := SUBSTR (p_str, i, j - i); 
i := j + len1; 
str_split.EXTEND; 
str_split (str_split.COUNT) := str; 
END IF; 
END LOOP; RETURN str_split; 
END fn_split; 
/ 测试： DECLARE 
CURSOR c 
IS 
SELECT * 
FROM TABLE (CAST (fn_split ('1;;12;;123;;1234;;12345', ';;') AS ty_str_split 
) 
); r c%ROWTYPE; 
BEGIN 
OPEN c; LOOP 
FETCH c INTO r; EXIT WHEN c%NOTFOUND; 
DBMS_OUTPUT.put_line (r.column_value); 
END LOOP; CLOSE c; 
END; 
/ 把A表变成id_no 和fn_split（id_no）两列
然后
................

Answer 9

刚刚弄 好了一个oracle的扯分函数如下：--创建类型
create or replace type type_split as table of varchar2(50);
--创建function
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('001|009','|'));明天再看如果用这个函数得到A一列的中的各个id_no在B表中的 num 之和>30的所有数据行。

Answer 10

请问下,创建好了扯分函数之后怎么操作阿,我的目的是将A中满足的各个id_no在B表中的 num 之和>30 的才提取出来 ,
谢谢再帮下哈!!!

Answer 11

不知道我这样写得出的结果对不对 ,
希望高人指点下 :
select * from A a
where exists (select sum(num) from B b where substr(a.phone_all,1,3)=b.msisdn 
                     or substr(a.id_no,5,3)=b.msisdn 
                     or substr(a.id_no,9,3)=b.msisdn
                     or substr(a.id_no,13,3)=b.msisdn
                     or substr(a.id_no,17,3)=b.msisdn
                     or substr(a.id_no,21,3)=b.msisdn having sum(num)>30);

Answer 12

真是服了楼主了，我第一个回的帖就可以满足你的要求啊，你可以测试啊：
select a.id_no, sum(b.num) from b,a 
where instr(a.id_no,b.id_no)>0 
group by a.id_no having sum(num)>30;

Answer 13

我试了下 ,select * from A a
where exists (select sum(num) from B b where substr(a.phone_all,1,3)=b.msisdn 
                     or substr(a.id_no,5,3)=b.msisdn 
                     or substr(a.id_no,9,3)=b.msisdn
                     or substr(a.id_no,13,3)=b.msisdn
                     or substr(a.id_no,17,3)=b.msisdn
                     or substr(a.id_no,21,3)=b.msisdn having sum(num)>30);
也是可以的 就是效率太慢了