本文共 2072 字，大约阅读时间需要 6 分钟。

之前的方法已经基本上能满足大部分匹配地域的需求了，但是唯一一点就是，效率太低了，dict_cities表数据不算很多，但也有几千条数据，然后在cte中调用后，结果执行过程中，这个表读取的太太太频繁了，造成效率大大不如人意

但是，在创建自定义函数的时候，有没有办法使用临时表、全局临时表，也不能执行存储过程，这个该怎么办。。。。好痛苦

先用表变量代替一下吧

下边是自定义函数的完整版

CREATE FUNCTION [dbo].[PickCityId] (	@xml xml)RETURNS intASBEGIN	DECLARE @id int	declare @d table(addr nvarchar(max),wsn int)	insert into @d	select 	replace(match,'杨凌','杨陵') as addr	,sn as wsn	from master.dbo.regexmatches(convert(nvarchar(max),@xml),'(?<=\w+=")[^"]+')	declare @t1 table(city_id int,city_path varchar(100),loc nvarchar(100))	insert into @t1	select city_id,city_path,isnull(		(case 			when master.dbo.regexismatch(isnull(abbr,''),'[a-z]')=1 			then city_name else abbr end		)		,master.dbo.regexreplace(city_name,'[矿新]区$|自治.*$|[县区]$','')	) as loc 	from dict_cities ci with (nolock) 	where city_del=0 	and isnull(targetId,0)=0	declare @t2 table(		addr nvarchar(max),wsn int,city_id int,city_path varchar(100),loc nvarchar(100)		,sn int,match nvarchar(100),pos int,mlen int	)	insert into @t2	select * 	from @d	cross apply (		select *,datalength(loc) as mlen 		from @t1		cross apply (			select * from master.dbo.RegexMatches(addr,loc)		) b		where datalength(loc)>0	) b	declare @t3 table(city_id int,city_path varchar(100),sn int,pos int,mlen int,lv int,wsn int,match nvarchar(100))	insert into @t3	select city_id,city_path,sn,pos,mlen,lv,wsn,match 	from @t2	cross apply (		select count(0) as lv 		from master.dbo.RegexMatches(city_path,'[\|]')	) b	declare @t4 table(city_id int,city_path varchar(100),sn int,pos int,mlen int,lv int,wsn int,match nvarchar(100))	insert into @t4	select top 1 * from @t3 order by pos,mlen desc,lv	declare @t5 table(city_id int,city_path varchar(100),sn int,pos int,mlen int,lv int,wsn int,match nvarchar(100))	insert into @t5	select t3.*	from @t4 t4	left join @t3 t3 on charindex(t4.city_path,t3.city_path)>0	where not (t3.lv<>t4.lv and t3.pos=t4.pos and t3.mlen
   
    a.city_id and charindex(city_path,a.city_path)>0		) b	)	select @id = (		select top 1 city_id		from t6 a		order by a.p desc,same,pos	)	RETURN @idEND
   

如果有人能解决效率问题，那就十分感谢啊

转载地址：http://hxvxi.baihongyu.com/