SQL statement Summary of various World Cup conjecture lists

/* Ranking of increase/decrease */
Copy codeThe Code is as follows:
Select top 50 UserName, sum (ReceivePrice)-sum (GuessPrice) as ReceivePrice,
Cast (sum (case when ReceivePrice> 0 THEN 1.0 ELSE 0 END)/count (ReceivePrice) * 100 as numeric () as Rate
From [game_FantasyLog]
WHERE IsJudge = 1
Group by UserId, UserName
Order by sum (ReceivePrice)-sum (GuessPrice) ASC

/* Ranking of error rate */
Copy codeThe Code is as follows:
Select top 50 UserName, sum (ReceivePrice)-sum (GuessPrice) as ReceivePrice,
Cast (sum (case when ReceivePrice> 0 THEN 1.0 ELSE 0 END)/count (ReceivePrice) * 100 as numeric () as Rate
From [game_FantasyLog]
WHERE IsJudge = 1
Group by UserId, UserName Having count (UserId)> = 5
Order by cast (sum (case when ReceivePrice> 0 THEN 1.0 ELSE 0 END)/count (ReceivePrice) * 100 as numeric () ASC

/* Top ranking */
Copy codeThe Code is as follows:
Select top 50 l. UserName, sum (l. GuessPrice), sum (l. ReceivePrice), f. title
From [game_FantasyLog] l left join [game_fantasy] f on l. topicid = f. id
Group by l. TopicId, l. UserName, f. title
Order by sum (l. GuessPrice) DESC

/* Ranking of cold scenes */
Copy codeThe Code is as follows:
Select top 50 f. id, f. title, f. GuessPrice, (select sum (receivePrice) FROM [game_FantasyLog] l where l. topicid = f. id ),
Cast (select sum (case when ReceivePrice> 0 THEN 1.0 ELSE 0 END)/f. GuessTimes FROM [game_FantasyLog] l2 where l2.topicid = f. id) as numeric ))
From [game_Fantasy] f WHERE f. GuessPrice> 1000
Order by (select sum (receivePrice) FROM [game_FantasyLog] l where l. topicid = f. id) ASC

/* Hits of the number of cold calls */
Copy codeThe Code is as follows:
Select top 50 UserName, sum (ReceivePrice) as ReceivePrice
From [game_FantasyLog] where topicid = 29
Group by TopicId, UserName
Order by sum (ReceivePrice) DESC