select * from tblPur where charindex('@a',fCName)>0 union all select * from tblPur where charindex('@b',fCName)>0 union all select * from tblPur where charindex('@c',fCName)>0
把* 替换掉,会在高点,其它高效的想不出来了select fCName,fPrice from tblPur where charindex('@a',fCName)>0 union all select fCName,fPrice from tblPur where charindex('@b',fCName)>0 union all select fCName,fPrice from tblPur where charindex('@c',fCName)>0
可能让大家误解了,是@A @B @C全部包含的最大价格。
select * from tblPur t where not exists(select 1 from tb where fCName=t.fCName and fPrice>t.fPrice) and fCName in (@A,@B,@C)
SELECT MAX(PRICE)PRICE FROM (select * from tblPur where charindex('@a',fCName)>0 union all select * from tblPur where charindex('@b',fCName)>0 union all select * from tblPur where charindex('@c',fCName)>0)AS T
select Max(case when charindex(@A,fCName)>0 and charindex(@B,fCName)>0 and charindex(@C,fCName)>0 then fPrice else 0 end ) from tblPur 现在使用以上语句,还是太慢,请各位帮帮忙。
--使用到charindex , like的话,索引基本无效. select max(fPrice) from tblPur where charindex('@a',fCName ) > 0 or charindex('@b',fCName ) or charindex('@c',fCName )select max(fPrice) from tblPur where fCName like '%@a%' or fCName like '%@b%' or fCName like '%@c%'
乌龟大侠OR的效率比UNION ALL要低点吧,不过索引都没效了
select max(price) from tb where fCName like '%@a%@b%@c%'
大家帮帮忙啊,tblPur只有2万条数据,查询大约100次要13秒左右,太慢了啊
学习 我以为 charindex 会用到索引的 呵呵
这个靠SQL语句来优化的空间不大了。。
select max(fPrice) from tblPur where charindex(@A,fCName)>0 and charindex(@B,fCName)>0 and charindex(@C,fCName)>0;
union all
select * from tblPur where charindex('@b',fCName)>0
union all
select * from tblPur where charindex('@c',fCName)>0
union all
select fCName,fPrice from tblPur where charindex('@b',fCName)>0
union all
select fCName,fPrice from tblPur where charindex('@c',fCName)>0
*
from
tblPur t
where
not exists(select 1 from tb where fCName=t.fCName and fPrice>t.fPrice) and fCName in (@A,@B,@C)
(select * from tblPur where charindex('@a',fCName)>0
union all
select * from tblPur where charindex('@b',fCName)>0
union all
select * from tblPur where charindex('@c',fCName)>0)AS T
--使用到charindex , like的话,索引基本无效.
select max(fPrice) from tblPur where charindex('@a',fCName ) > 0 or charindex('@b',fCName ) or charindex('@c',fCName )select max(fPrice) from tblPur where fCName like '%@a%' or fCName like '%@b%' or fCName like '%@c%'
from tblPur
where charindex(@A,fCName)>0
and charindex(@B,fCName)>0
and charindex(@C,fCName)>0;
--11楼这样是不对的:@a、@b、@c没有顺序之分!