在线等!查询同列最大值并比较大小
有如下表结构
col1 col2
5 20
6 16
7 10
现在要在col2中查询数据,当查询出来的数据要小于同列中最大值的3/4时 col1取0,并按col1排序
即
col1 col2
6 16
5 20
0 10
[解决办法]
declare @t table (col1 int,col2 int)insert into @tselect 5,20 union allselect 6,16 union allselect 7,10select 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 165 200 10*/
[解决办法]
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.GameIDFROM 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.MatchGameIDWhere 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)
