SQL statement Summary of various World Cup conjecture lists

/* Ranking of increase/decrease */ CopyCode The 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