A summary of the SQL statements of various lists of World Cup guessing activities _mssql

* * Increase in the decline of the ranking *

Copy Code code as follows:

Select top Username,sum (receiveprice)-sum (guessprice) as Receiveprice,
Cast (sum (case when receiveprice>0 THEN 1.0 ELSE 0)/COUNT (receiveprice) * as numeric (4,1)) as Rate
from [Game_fantasylog]
WHERE isjudge=1
GROUP by Userid,username
ORDER by SUM (receiveprice)-sum (guessprice) ASC

/* Correct rate Error Rate ranking * *

Copy Code code as follows:

Select top Username,sum (receiveprice)-sum (guessprice) as Receiveprice,
Cast (sum (case when receiveprice>0 THEN 1.0 ELSE 0)/COUNT (receiveprice) * as numeric (4,1)) as Rate
from [Game_fantasylog]
WHERE isjudge=1
GROUP by Userid,username has count (UserId) >= 5
Order BY CAST (sum (case when receiveprice>0 THEN 1.0 ELSE 0)/COUNT (receiveprice) * as numeric (4,1)) ASC

* * Grand Ranking * *

Copy Code code as follows:

Select top 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

/* Unpopular Number ranking * *

Copy Code code as follows:

Select Top 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.topici D = f.id) as numeric (4,2))
From [Game_fantasy] f WHERE f.guessprice > 1000
Order BY (select SUM (receiveprice) from [Game_fantasylog] l where l.topicid = f.id) ASC

* * The hit of the unpopular games

Copy Code code as follows:

Select top Username,sum (receiveprice) as Receiveprice
From [Game_fantasylog] where topicid=29
GROUP by Topicid,username
ORDER by sum (receiveprice) DESC