create proc proc_test
@UserName varchar(50)
as
if len(@UserName)=6 --小灵通几位号这里就输入几位
begin
--小灵能查询
end
else
begin
select Province,Owner from c where Start_Num<@UserName and End_Num =<@UserName
end
@UserName varchar(50)
as
if len(@UserName)=6 --小灵通几位号这里就输入几位
begin
--小灵能查询
end
else
begin
select Province,Owner from c where Start_Num<@UserName and End_Num =<@UserName
end
在这里看来,应该用模糊查询吧查询@UserName在B表中相似的部份,
只用关注City_Code 这一列
因为 小灵通号码是 037139714122 ,03756525147,这样的形式
也就是说 如果用户名是以0开头的则截取前4位和 city_code 这一列做比较最后的结果应该为username password city
03716167433 adfasdf 郑州
03755565550 dasfsa 平顶山
13939039191 dfsdf 河南郑州
13026584258 asdfsd 河北石家庄
…………… ………… …………
…………… ……………… ………………
else b.City
end
from a
left join c
on (a.username bewteen c.start_num and c.end_num) and a.username like '13%'
left join b
on left(a.username,4)=b.City_Code and a.username not like '13%'
then
(select City from b where City_Code=left(a.UserName,4))
else
(select City from c where UserName between Start_Num and End_Num)
end
)
from a
--小灵通位数是11位吗?
上面没有测试
then
(select City from Henan_xlt where City_Code=left(a.UserName,4))
else
(select City from Phone where UserName between Start_Num and End_Num)
end
)
from Accounts_User a--结果
03716167433 adfasdf 郑州市
03735565550 dasfsa 新乡市
13000210001 dfsdf 上海
13000250022 asdfsd 南京
then
(select City from Henan_xlt where City_Code=left(a.UserName,4))
else
(select City from Phone where UserName between Start_Num and End_Num)
end
)
from Accounts_User atable a 中现在有800多条数据,查询时间已经在10秒以上了
现在我对原来的表建立了索引如下:
Henan_xlt 设置 City_Code 为主键
phone 设置 Start_Num 和 End_Num 为唯一索引 看哪位有比较好的优化建议 ?