@selectIndex 是传入的参数 如果是0 就模糊查 LoginName 对应的值 @searchfilterand LoginName like case @selectIndex when 0 then '%'+@searchfilter+'%' else LoginName endand f.Agentid like case @selectIndex when 1 then '%'+@searchfilter+'%' else f.Agentid endand u.Tel like case @selectIndex when 2 then '%'+@searchfilter+'%' else u.Tel endand b.CName like case @selectIndex when 3 then '%'+@searchfilter+'%' else b.CName endand u.Mobile like case @selectIndex when 4 then '%'+@searchfilter+'%' else u.Mobile endand u.UserName like case @selectIndex when 5 then '%'+@searchfilter+'%' else u.UserName endand b.[Address] like case @selectIndex when 6 then '%'+@searchfilter+'%' else b.[Address] end这样似乎很慢如何优化
我这里只是查询的一部分 通过@selectIndex 来看 要查哪个字段的值
if (@selectIndex = 0)
{
@sortField ='sadfsdafasdf';
}
...................................
if 还没有case when then 效率高哦.........
这样的话用if 可以少走几行代码
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
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 这样写有错误吧???
(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 附近错误所以请教了别人的查询 这个也不错 这个不错哦