日期:2014-05-17 浏览次数:20668 次
ALTER PROCEDURE [dbo].[UP_SEARCH_SIMILARITY]
@userID int=7
as
BEGIN
set statistics time on
Declare @Similarity Table(UserID int, Similarity decimal(4,3))
Insert into @Similarity
select up1.UserID,sum(case when up1.weight*up2.weight>0 then 1.0 else 0 end)/count(up1.UserID) as Similarity from [UT_PROGRAM] up1
join (select ProgramID,weight from [UT_PROGRAM] where userid=@userID) up2 on up1.ProgramID=up2.ProgramID
--where up1.UserID<>@userID 为增加效率 在下面语句排除自己
group by up1.UserID having sum(case when up1.weight*up2.weight>0 then 1 else 0 end)>0
select u.[UserID],[Email],[Nick],[HeaderPath],[Sex],[Birthday],[Longitude],[Latitude],s.Similarity from[UT_USER] u
join @Similarity s on s.UserID=u.UserID and s.UserID<>@userID
--where s.UserID<>@userID and Similarity>0
order by Similarity desc
END
ALTER PROCEDURE [dbo].[UP_SEARCH_SIMILARITY]
@userID int=7
as
BEGIN
--set statistics time on
CREATE TABLE #Similarity (UserID int, Similarity decimal(4,3))
select ProgramID,weight
INTO #T
from [UT_PROGRAM]
where userid=@userID
Insert into #Similarity
select up1.UserID,sum(case when up1.weight*up2.weight>0 then 1.0 else 0 end)/count(up1.UserID) as Similarity
from [UT_PROGRAM] up1 join #T up2
on up1.ProgramID=up2.ProgramID
where up1.UserID<>@userID
group by up1.UserID
having sum(case when up1.weight*up2.weight>0 then 1 else 0 end)>0
select u.[UserID],[Email],[Nick],[HeaderPath],[Sex],[Birthday],[Longitude],[Latitude],s.Similarity
from[UT_USER] u join #Similarity s
on s.UserID=u.UserID
order by Similarity desc
END