存储过程练习
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