3种方式实现的母语优先匹配查询的存储过程

说明：会员会的语言字段不可重复，不然会有点问题(不过也是可解决的哈，但最好不要^-^)！

比如：English|0,English|1,English|2是不允许出现的情况！

 

关键点：1.获取会员想学的第一种语言

            2.依次按照母语，高级，中级，初级的顺序匹配会员会的语言并按照会员在线次数分别排序

 

 

/* 返回会员想学习的第一个语言 star */ create function dbo.fn_GCC_GetLanguage ( @languageString varchar(250) ) --return @languageArrayTable table (language varchar(250)) --返回表变量 returns varchar(50) --返回字串 as begin declare @splitePosition int select @splitePosition=charindex('|',@languageString) --insert into @languageArrayTable value('English') --返回表变量 --return return left(@languageString,@splitePosition - 1) --返回字串 end /* 返回会员想学习的第一个语言 end */ --调用函数测试 select dbo.fn_GCC_GetLanguage('English|0,China|1') /* 存储过程功能说明： 根据会员想学的第一个语言和 另一会员会的语言(母语优先) 匹配查询，并根据会员在线时 间排序，取前6 */ --style_1:可搜索出所有满足条件的会员 create proc pr_GCC_SelectRightUser ( @uid int ) as begin declare @studylanguage varchar(250) --会员想学习的全部语言 declare @firstStuLan varchar(50) --会员想学习的第一种语言 declare @i int --循环用计数器^-^ --获取会员想学的全部语言 select @studylanguage=[studylanguage] from [gcc_userinfo] where [uid]=@uid --获取会员想学的第一种语言 select @firstStuLan=dbo.fn_GCC_GetLanguage(@studylanguage) --循环计数器初始化 set @i = 0 --创建临时表（存储满足配对条件的会员信息，方便分页^-^） create table #knowUser ( uid int, nickname varchar(40), isimg bit, sex bit, otime int, bday datetime, sightml varchar(100), comwCountry varchar(50), comwCity varchar(50), knowlanguage varchar(250), studylanguage varchar(250) ) --从母语开始(顺序：母语，高级，中级，初级)依次查询满足配对条件的会员 while (select @i) < 4 begin insert into [#knowUser] select uid,nickname,isimg,sex,otime,bday,sightml,comwCountry,comwCity,knowlanguage,studylanguage from [gcc_userinfo] where [knowlanguage] like '%'+@firstStuLan+'|'+ convert(varchar(4),@i) + '%' order by otime desc --排序 set @i=@i+1 end --查询临时表 select * from [#knowUser] --销毁临时表 drop table [#knowUser] end --调用存储过程测试 exec pr_GCC_SelectRightUser 1 --style_2:只搜索前6条满足条件的会员 create proc pr_GCC_SelectRightUser_2 ( @uid int ) as begin declare @studylanguage varchar(250) --会员想学习的全部语言 declare @firstStuLan varchar(50) --会员想学习的第一种语言 declare @i int --循环用计数器^-^ declare @count int --获取临时表中记录数 --获取会员想学的全部语言 select @studylanguage=[studylanguage] from [gcc_userinfo] where [uid]=@uid --获取会员想学的第一种语言 select @firstStuLan=dbo.fn_GCC_GetLanguage(@studylanguage) --循环计数器初始化 set @i = 0 --创建临时表（存储满足配对条件的会员信息，方便分页^-^） create table #knowUser ( uid int, nickname varchar(40), isimg bit, sex bit, otime int, bday datetime, sightml varchar(100), comwCountry varchar(50), comwCity varchar(50), knowlanguage varchar(250), studylanguage varchar(250) ) --最大限度的获取6条满足配对条件的会员(母语优先匹配) while (select count(0) from [#knowUser]) < 6 begin --未依次匹配条件(顺序：母语，高级，中级，初级)结束 if @i = 0 begin insert into [#knowUser] select top 6 uid,nickname,isimg,sex,otime,bday,sightml,comwCountry,comwCity,knowlanguage,studylanguage from [gcc_userinfo] where [knowlanguage] like '%'+@firstStuLan+'|'+ convert(varchar(4),@i) + '%' order by otime desc --排序 set @i=@i+1 end else if @i > 3 begin break --跳出循环 end else begin --获取临时表中记录总数 select @count=count(0) from [#knowUser] set @count = 6 - @count --解决ms server2000不支持top后接变量的问题^-^ exec('insert into [#knowUser] select top '+ @count +' uid,nickname,isimg,sex,otime,bday,sightml,comwCountry,comwCity,knowlanguage,studylanguage from [gcc_userinfo] where [knowlanguage] like ''%'+ @firstStuLan + '|'+ @i +'%'' order by otime desc') set @i=@i+1 end end --查询临时表 select * from [#knowUser] --销毁临时表 drop table [#knowUser] end --调用存储过程测试 exec pr_GCC_SelectRightUser_2 1 --style_3:从母语，高级，中级，初级中各搜索6条满足条件的会员 create proc pr_GCC_SelectRightUser_3 ( @uid int ) as begin declare @studylanguage varchar(250) --会员想学习的全部语言 declare @firstStuLan varchar(50) --会员想学习的第一种语言 declare @i int --循环用计数器^-^ --获取会员想学的全部语言 select @studylanguage=[studylanguage] from [gcc_userinfo] where [uid]=@uid --获取会员想学的第一种语言 select @firstStuLan=dbo.fn_GCC_GetLanguage(@studylanguage) --循环计数器初始化 set @i = 0 --创建临时表（存储满足配对条件的会员信息，方便分页^-^） create table #knowUser ( uid int, nickname varchar(40), isimg bit, sex bit, otime int, bday datetime, sightml varchar(100), comwCountry varchar(50), comwCity varchar(50), knowlanguage varchar(250), studylanguage varchar(250) ) --从母语，高级，中级，初级中各搜索6条满足条件的会员 while (select @i) < 4 begin insert into [#knowUser] select top 6 uid,nickname,isimg,sex,otime,bday,sightml,comwCountry,comwCity,knowlanguage,studylanguage from [gcc_userinfo] where [knowlanguage] like '%'+@firstStuLan+'|'+ convert(varchar(4),@i) + '%' order by otime desc --排序 set @i=@i+1 end --查询临时表 select * from [#knowUser] --销毁临时表 drop table [#knowUser] end --调用存储过程测试 exec pr_GCC_SelectRightUser_3 1