declare @t table (col1 int,col2 int)
insert into @t
select 5,20 union all
select 6,16 union all
select 7,10

select col1=case when col2<col3 then 0 else col1 end,col2 
from (select *,(select max(col2)*0.75 from @t) col3 from @t b
) aa order by 1 desc
/*
col1        col2
----------- -----------
6           16
5           20
0           10
*/

------解决方案--------------------

SQL code

ALTER Procedure [dbo].[WTM_PersonalResult_GetAllPersonalResultViewByGameIDAndMatchGameName]
@OrderName varchar(1),
@GameID int,
@MatchGameName nvarchar(5)
AS
 
 with ta as
 (
SELECT  dbo.WTM_Order.Score, 
        dbo.WTM_GamePlan.PlayerNumber, 
        dbo.WTM_Player.PlayerName, 
        dbo.WTM_Team.TeamName, 
        dbo.WTM_PersonalResult.PersonalResultID, 
        dbo.WTM_PersonalResult.GamePlaneID, 
        dbo.WTM_PersonalResult.MatchGameID, 
        dbo.WTM_PersonalResult.Rank, 
        dbo.WTM_PersonalResult.TotalLoop, 
        dbo.WTM_PersonalResult.TotalTime, 
        dbo.WTM_PersonalResult.BestLoop, 
        dbo.WTM_PersonalResult.BestLoopTime, 
        dbo.WTM_PersonalResult.IsValid, 
        dbo.WTM_MatchGame.MatchGameName, 
        dbo.WTM_MatchGame.GameID
FROM         dbo.WTM_Player INNER JOIN
             dbo.WTM_GamePlan ON dbo.WTM_Player.PlayerID = dbo.WTM_GamePlan.PlayerID INNER JOIN
             dbo.WTM_PersonalResult ON dbo.WTM_GamePlan.GamePlanID = dbo.WTM_PersonalResult.GamePlaneID INNER JOIN
             dbo.WTM_Team ON dbo.WTM_Player.TeamID = dbo.WTM_Team.TeamID INNER JOIN
             dbo.WTM_Order ON dbo.WTM_PersonalResult.Rank = dbo.WTM_Order.Rank INNER JOIN
             dbo.WTM_MatchGame ON dbo.WTM_PersonalResult.MatchGameID = dbo.WTM_MatchGame.MatchGameID
Where dbo.WTM_Order.OrderName=@OrderName 
And dbo.WTM_GamePlan.GameID=@GameID 
And dbo.WTM_MatchGame.MatchGameName=@MatchGameName
) 
 
Select PlayerNumber,PlayerName,TeamName, 
       PersonalResultID, GamePlaneID, MatchGameID, 
       TotalLoop, TotalTime, BestLoop, BestLoopTime, 
       IsValid, MatchGameName, GameID,
       
      [Rank] =(case when TotalLoop<(select MAX(TotalLoop) From ta)*3/4 then 0 else [Rank] end)
                
From ta 
 
ORDER BY IsValid desc,(Case When tb.Rank<>0 then tb.Rank else 254 end)