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

vstian 2009-03-10 04:31:40

有这样一张表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才出来,
怎么弄阿 ,我说了清楚吗..........

...全文

162 17 打赏收藏转发到动态举报

写回复

用AI写文章

17 条回复

切换为时间正序

请发表友善的回复…

发表回复

BrainStormLee 2009-03-11

打赏
举报

mark!~ 字符串扯分

vstian 2009-03-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);
也是可以的就是效率太慢了

vstian 2009-03-11

打赏
举报

太棒了,楼上的可以阿.
就是执行的太慢了...给分了哈

vstian 2009-03-11

打赏
举报

楼上真的阿我看看. 你先等等 ....

randy_ideal 2009-03-11

打赏
举报

真是服了楼主了，我第一个回的帖就可以满足你的要求啊，你可以测试啊：
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;

vstian 2009-03-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);

vstian 2009-03-11

打赏
举报

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

谢谢再帮下哈!!!

oraclelogan 2009-03-10

打赏
举报

刚刚弄好了一个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的所有数据行。

cool_hnu 2009-03-10

打赏
举报

想半天不知道思路对不
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）两列
然后
................

cool_hnu 2009-03-10

打赏
举报

阿三 2009-03-10

打赏
举报

我这里有一个以前收集的别人写的函数你可以参照一下，这是从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;

请输入... 2009-03-10

打赏
举报



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我不知道你要的是不是我这个意思

randy_ideal 2009-03-10

打赏
举报

二楼结果不合你要求吗？

oraclelogan 2009-03-10

打赏
举报

我想应该会用到一个oracle里面的一个对字符串扯分的函数，只是一时想不起来函数叫啥名字了

扯分之后放到一个临时表里面，然后从这个临时表里面取数据就ok。

但是这个函数一时忘记了，自己去实现的话，要写一个存储函数，又要测试，比较烦

下班回去之后，如果还没有结贴的话，就去查资料帮你解决下吧。业务不是很难就是那个专用函数我忘记叫啥名字了！

vstian 2009-03-10

打赏
举报

楼上的 .我的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| 这样子

求教....

cgwshxs 2009-03-10

打赏
举报

不是很懂你的意思
如果表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;

randy_ideal 2009-03-10