说明:会员会的语言字段不可重复,不然会有点问题(不过也是可解决的哈,但最好不要^-^)!
比如:English|0,English|1,English|2是不允许出现的情况!
关键点:1.获取会员想学的第一种语言
2.依次按照母语,高级,中级,初级的顺序匹配会员会的语言并按照会员在线次数分别排序
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