现在A表中有3条记录
ID TYPENAME
1 ab,cd,ef
2 ab,ef
3 gg,hi要求用SQL语句查询同时包含ab,ef的记录(不用嵌套查询)
我用sql语句
select * from A where typename like '%ab%' and typename like '%ef%' 为什么查询出来总是空为此我写了一个FUNCTION 功能为查询v_a是否包含v_b,且验证正确
select checks('ab,cd,ef','ab,ef') from dual ----值为1
select checks('gg,hi','ab,ef') from dual ----值为0但是我一用到这里select * from A where checks(typename,'ab,ef')=1 为什么查询出来也总是空
请各位高手指点,万分感谢CREATE OR REPLACE function TEST.checks(v_a varchar2,v_b varchar)
return number
as
v_number number;
v_length number ;
v_st number :=1;
v_index number;
v_str VARCHAR2(150);
begin
v_length := length(v_b);
while (v_st <= v_length) loop
v_index := INSTR(v_b,',',v_st) ;
if v_index = 0 then
v_str := substr(v_b,v_st);
select instr(v_a,v_str) into v_number from dual ;
if v_number = 0 then
return 0;
end if;
v_st :=v_length+1;
else
v_str := substr(v_b,v_st,v_index-v_st);
select instr(v_a,v_str) into v_number from dual ;
if v_number = 0 then
return 0;
end if;
v_st :=v_index+1;
end if ;
end loop;
return 1;
end;
ID TYPENAME
1 ab,cd,ef
2 ab,ef
3 gg,hi要求用SQL语句查询同时包含ab,ef的记录(不用嵌套查询)
我用sql语句
select * from A where typename like '%ab%' and typename like '%ef%' 为什么查询出来总是空为此我写了一个FUNCTION 功能为查询v_a是否包含v_b,且验证正确
select checks('ab,cd,ef','ab,ef') from dual ----值为1
select checks('gg,hi','ab,ef') from dual ----值为0但是我一用到这里select * from A where checks(typename,'ab,ef')=1 为什么查询出来也总是空
请各位高手指点,万分感谢CREATE OR REPLACE function TEST.checks(v_a varchar2,v_b varchar)
return number
as
v_number number;
v_length number ;
v_st number :=1;
v_index number;
v_str VARCHAR2(150);
begin
v_length := length(v_b);
while (v_st <= v_length) loop
v_index := INSTR(v_b,',',v_st) ;
if v_index = 0 then
v_str := substr(v_b,v_st);
select instr(v_a,v_str) into v_number from dual ;
if v_number = 0 then
return 0;
end if;
v_st :=v_length+1;
else
v_str := substr(v_b,v_st,v_index-v_st);
select instr(v_a,v_str) into v_number from dual ;
if v_number = 0 then
return 0;
end if;
v_st :=v_index+1;
end if ;
end loop;
return 1;
end;
select * from table1 where name like '%ab%' and name like '%ef%'
select * from ( SELECT DISTINCT ID,NAME,DESCRIPTION,CODE,PERMITUSER,EXAMINER,FTYPENAME,FTYPEID, str_list('ID',VW_FILESINFO.id,'versionname','VW_FILESINFO') versionname,
str_list('ID',VW_FILESINFO.id,'versionid','VW_FILESINFO') versionid, DEPTNAME,DEPTID,USERID,USERREALNAME,str_list('ID',VW_FILESINFO.id,'fcodetypeid','VW_FILESINFO') FCODETYPEID,
str_list('ID',VW_FILESINFO.id,'fcodetypename','VW_FILESINFO') FCODETYPENAME,FLEVELID,FLEVELNAME,CREATETIME,flevelright,deptright,fcodetyperight FROM VW_FILESINFO
) where flevelright=1 and deptright=1 and fcodetyperight=1 and checks(fcodeTypeName,'质量GB/T19001,质量ISO/TS16949')=1这个可以查到SELECT DISTINCT ID,NAME,DESCRIPTION,CODE,PERMITUSER,EXAMINER,FTYPENAME,FTYPEID, str_list('ID',VW_FILESINFO.id,'versionname','VW_FILESINFO') versionname,
str_list('ID',VW_FILESINFO.id,'versionid','VW_FILESINFO') versionid, DEPTNAME,DEPTID,USERID,USERREALNAME,str_list('ID',VW_FILESINFO.id,'fcodetypeid','VW_FILESINFO') FCODETYPEID,
str_list('ID',VW_FILESINFO.id,'fcodetypename','VW_FILESINFO') FCODETYPENAME,FLEVELID,FLEVELNAME,CREATETIME,flevelright,deptright,fcodetyperight FROM VW_FILESINFO
where flevelright=1 and deptright=1 and fcodetyperight=1 and checks(fcodeTypeName,'质量GB/T19001,质量ISO/TS16949')=1
这个就查不到了
楼主新增一条记录,手工输入 ab,ef 再看一下。