17,086
社区成员
发帖
与我相关
我的任务
分享
SELECT a.ty,a.itemno,a.qty
FROM a,b
WHERE a.atype=b.btype
and a.packno<>'0'
and ((para1 is null and para2 is null and rownum<1)
or (para1 is null and para2 is not null and instr(para2,b.btype)>0)
or (para1 is not null and para2 is null and instr(para1,a.atype)>0)
or (para1 is not null and para2 is not null and (instr(para2,b.btype)>0 or instr(para1,a.atype)>0))
ORDER BY a.ty ASC;
大表的话性能不好的,如果可以还是拼接动态SQL吧SELECT a.ty,a.itemno,a.qty
FROM a,b
WHERE a.atype=b.btype
and a.packno<>'0' and (CASE para1 is null
THEN ' rownum<1'
ELSE 'instr(para1,atype)>0'
END) ORDER BY a.ty ASC;
select
case when para1 is null and para2 is null then ' rownum<1'
when para1 is null and para2 is not null then ' instr(para2,bb.btype)>0'
when para1 is not null and para2 is null then ' instr(para1,aa.atype)>0'
else ' instr(para2,bb.btype)>0 or instr(para1,aa.atype)>0'
end tj into v_tj
from dual;
v_where:=v_where||' and '||v_tj
大概意思这样