高分求助@@@关于LIKE~~~~

[sprite18]

我有一SQL语句

select sum(case when fjd = 'J' then fbal else -fbal end) as QsJe
from T001_Laccount a right join t001_fcwvch b
on a.fportfoliocode=b.fportfoliocode and a.facctcode=b.fkmh and a.fyear=b.fyear and a.fyear='2006' and a.FAcctClass = '资产类'
where A.fportfoliocode in ('000001', '000004') and
(a.FAcctAttr like '银行存款%' or a.FAcctAttr like '清算备付金%' or a.FAcctAttr like '最低备付金%')
AND B.FDate between to_date('2006-01-01', 'yyyy-MM-dd') and to_date('2006-06-30', 'yyyy-MM-dd')

这条语句执行起来很慢,如果我把条件
a.FAcctAttr like '银行存款%' or a.FAcctAttr like '清算备付金%' or a.FAcctAttr like '最低备付金%'

改为单一LIKE
a.FAcctAttr like '银行存款%'

速度快了很多很多,只有0.0几秒而已

那我想问题是在LIKE上,或者是FACCTATTR上

可是
1,除了LIKE我不知道还有什么其他写法可以满足我现在的这种需求
2,我想到过设置索引,但FACCTATTR是一个重复数据很多的列,设置索引应该不起什么作用

不知道我说的对不对,各位还有什么好办法没有.

[Eric_1999]

try:

instr(a.FAcctAttr, 银行存款) or instr(a.FAcctAttr, 清算备付金) or instr(a.FAcctAttr, 最低备付金)

[sprite18]

instr?

提示错误,SQL里有这种写法吗?

[sprite18]

哦,忘了加''号
写法到是可以,但速度并没有多大变化,而且显示的结果也错误了

[AFIC]

%如果能改成固定个数的下划线的话，会稍微快一点

[honght]

试一下这个
'银行存款,清算备付金,最低备付金' like concat(concat('%',a.FAcctAttr),'%')

[sprite18]

谢谢楼上这位老兄
但速度还是一样

[wiler]

建议把or改为union all
如：
select sum(qsje) from (
select sum(case when fjd = 'J' then fbal else -fbal end) as QsJe
from T001_Laccount a right join t001_fcwvch b
on a.fportfoliocode=b.fportfoliocode and a.facctcode=b.fkmh and a.fyear=b.fyear and a.fyear='2006' and a.FAcctClass = '资产类'
where A.fportfoliocode in ('000001', '000004') and
a.FAcctAttr like '银行存款%'
AND B.FDate between to_date('2006-01-01', 'yyyy-MM-dd') and to_date('2006-06-30', 'yyyy-MM-dd')
union all
select sum(case when fjd = 'J' then fbal else -fbal end) as QsJe
from T001_Laccount a right join t001_fcwvch b
on a.fportfoliocode=b.fportfoliocode and a.facctcode=b.fkmh and a.fyear=b.fyear and a.fyear='2006' and a.FAcctClass = '资产类'
where A.fportfoliocode in ('000001', '000004') and
a.FAcctAttr like '清算备付金%'
AND B.FDate between to_date('2006-01-01', 'yyyy-MM-dd') and to_date('2006-06-30', 'yyyy-MM-dd')
union all
select sum(case when fjd = 'J' then fbal else -fbal end) as QsJe
from T001_Laccount a right join t001_fcwvch b
on a.fportfoliocode=b.fportfoliocode and a.facctcode=b.fkmh and a.fyear=b.fyear and a.fyear='2006' and a.FAcctClass = '资产类'
where A.fportfoliocode in ('000001', '000004') and
a.FAcctAttr like '最低备付金%'
AND B.FDate between to_date('2006-01-01', 'yyyy-MM-dd') and to_date('2006-06-30', 'yyyy-MM-dd')
)

[doer_ljy]

或者用Exist改写or

[sprite18]

但还是并没有感觉速度快了多少啊