如何优化这个模糊查询

屌丝女士111 2010-04-13 04:56:15





@selectIndex 是传入的参数 如果是0 就模糊查 LoginName 对应的值 @searchfilter



and LoginName like case @selectIndex when 0 then '%'+@searchfilter+'%' else LoginName end



and f.Agentid like case @selectIndex when 1 then '%'+@searchfilter+'%' else f.Agentid end



and u.Tel like case @selectIndex when 2 then '%'+@searchfilter+'%' else u.Tel end



and b.CName  like case @selectIndex when 3 then '%'+@searchfilter+'%' else b.CName end



and u.Mobile  like case @selectIndex when 4 then '%'+@searchfilter+'%' else u.Mobile end



and u.UserName  like case @selectIndex when 5 then '%'+@searchfilter+'%' else u.UserName end



and b.[Address] like case @selectIndex when 6 then '%'+@searchfilter+'%' else b.[Address] end

这样似乎很慢

如何优化

...全文

195 19 打赏收藏转发到动态举报

写回复

用AI写文章

19 条回复

切换为时间正序

请发表友善的回复…

发表回复

一一一一 2010-09-03

打赏
举报

学习了

屌丝女士111 2010-04-15

打赏
举报

((LoginName like '%'+@searchfilter+'%' AND @selectIndex=0) OR 

				(cast(f.Agentid  as varchar) like '%'+cast(@searchfilter as varchar)+'%' AND @selectIndex=1) OR

				(u.Tel like '%'+@searchfilter+'%' AND @selectIndex=2) OR

				(u.Mobile like '%'+@searchfilter+'%' AND @selectIndex=3) OR 

				(b.CName like '%'+@searchfilter+'%' AND @selectIndex=4) OR

				(u.UserName like '%'+@searchfilter+'%' AND @selectIndex=5) OR

				(b.[Address] like '%'+@searchfilter+'%' AND @selectIndex=6))

虽然11楼的可以但是那个写在我的查询里面的时候报like 附近错误

所以请教了别人的查询这个也不错

这个不错哦

Tony3820 2010-04-15

打赏
举报

zlqyizhizailushang 2010-04-15

打赏
举报

每个都走了，11楼的应该行

ZHUKY 2010-04-15

打赏
举报

顶顶顶.........Mark！~~

屌丝女士111 2010-04-15

打赏
举报

[Quote=引用 11 楼 jiangshun 的回复:]
SQL code
case @selectIndex
when 0 then LoginName like '%'+@searchfilter+'%'
when 1 then f.Agentid like '%'+@searchfilter+'%'
when 2 then u.Tel like '%'+@searchfilter+'%'
when 3 t……
[/Quote]

问问哦这个在一个完整的sql 里面如何写？？

select * from table where id=@id and

case @selectIndex
when 0 then LoginName like '%'+@searchfilter+'%'
when 1 then f.Agentid like '%'+@searchfilter+'%'
when 2 then u.Tel like '%'+@searchfilter+'%'
when 3 then b.CName like '%'+@searchfilter+'%'
when 4 then u.Mobile like '%'+@searchfilter+'%'
when 5 then u.UserName like '%'+@searchfilter+'%'
when 6 then b.[Address] like '%'+@searchfilter+'%'
end

这样写有错误吧？？？

Toney520 2010-04-14

打赏
举报

推荐11楼

屌丝女士111 2010-04-14

打赏
举报

jiangshun 2010-04-13

打赏
举报

case @selectIndex 

	when 0 then LoginName like '%'+@searchfilter+'%'

	when 1 then f.Agentid like '%'+@searchfilter+'%'

	when 2 then u.Tel like '%'+@searchfilter+'%'

	when 3 then b.CName like '%'+@searchfilter+'%'

	when 4 then u.Mobile like '%'+@searchfilter+'%'

	when 5 then u.UserName like '%'+@searchfilter+'%'

	when 6 then  b.[Address] like '%'+@searchfilter+'%'

end

cppinxp 2010-04-13

打赏
举报

适合在程序里做

屌丝女士111 2010-04-13

打赏
举报

[Quote=引用 8 楼 cyrix_wxm 的回复:]
我以为是根据不同index 查询不同的字段呢
这样的话用if 可以少走几行代码
[/Quote]

嗯 .............所以

cyrix_wxm 2010-04-13

打赏
举报

我以为是根据不同index 查询不同的字段呢
这样的话用if 可以少走几行代码

cyrix_wxm 2010-04-13

打赏
举报

我晕每个case when都要让走？

屌丝女士111 2010-04-13

打赏
举报

[Quote=引用 5 楼 cyrix_wxm 的回复:]
declare @sortField varchar(20)
if (@selectIndex = 0)
{
@sortField ='sadfsdafasdf';
}
[/Quote]

...................................
if 还没有case when then 效率高哦.........

cyrix_wxm 2010-04-13