存储过程练习

CREATE PROCEDURE Web_GetRank
@TeamID  int ,   --组别
@Num  int ,  --提取前几名
@Accounts varchar(31)= '**'   --查询的账号
WITH ENCRYPTION
AS

if  exists (select UserID 
from GameScoreInfo
where UserID not in (select UserID from MatchScoreStat) )
begin
insert into MatchScoreStat( UserID) select UserID 
from GameScoreInfo
where UserID not in (select UserID from MatchScoreStat)
end

select a.TeamID, a.Accounts,a.IdentitySerial,b.Score+c.MatchScore TotalScore,
RANK() over (order by b.Score+c.MatchScore desc,
b.WinCount+b.LostCount+b.FleeCount desc,
 ( case  (b.WinCount+b.LostCount+b.FleeCount+b.DrawCount) when 0 then 0 else   b.DrawCount/(b.WinCount+b.LostCount+b.FleeCount+b.DrawCount) end) asc) TotalRank
into #TempTableRank
from MatchUserDetail a ,GameScoreInfo b,dbo.MatchScoreStat c ,MatchUserInfo d
where  a.UserID= b.UserID and b.UserID=c.UserID  and a.UserID=d.UserID and d.StateFlag=0;

if @TeamID =0
begin
select top(@Num) Accounts ,IdentitySerial, TotalScore,TotalRank ,Rank() over (order by TotalRank) RankINGroup from #TempTableRank ;
end
else
begin
select top(@Num) Accounts ,IdentitySerial, TotalScore,TotalRank ,Rank() over (order by TotalRank) RankINGroup from #TempTableRank where TeamID=@TeamID;
end

if @Accounts<>'**'
begin
select @TeamID=  TeamID from MatchUserDetail where Accounts= @Accounts
select top(@Num) Accounts ,IdentitySerial, TotalScore,TotalRank ,Rank() over (order by TotalRank) RankINGroup  into #TempTableRank2 from #TempTableRank where TeamID=@TeamID;
select * from #TempTableRank2 where Accounts= @Accounts
end
go