Question

是否有办法计算已经拥有<代码>的一套记录中的记录数目。适用的组别

I have a table that contains a weekly summary of wins, ties and losses for all users in a series of games. It looks sort of like this:

userid  weekid  wins  losses  ties
1       1       10    5       2
1       2       11    3       3
1       3       8     9       0
2       1       11    6       0
2       2       7     9       1
2       3       9     7       1
3       1       3     14      0
3       2       7     9       1
3       3       6     9       2

I want to determine the rank of a given user. I would do this by counting the number of users with a better combination of wins, ties and losses than that user. If I wanted to determine the rank of user #3 for example, I could use this query, but it only gets me half way there:

SELECT COUNT(userid) 
FROM scores
GROUP BY userid
HAVING (SUM(wins) * 2) + SUM(ties) > 35

结果是:

COUNT(userid)
3
3

这使我感到有记录,有2个记录(他排名第三)。我真的喜欢的是记录数目(2)而不是记录本身。我认为的唯一事情是把记录放在临时桌上,并在那里打上<条形码>。或者,我可以把数据转回C#,并记录在那里,但如果特定用户有可怕的记录,我需要发送大量记录。

我希望能以相对廉价的方式做到这一点。

Answer 1

SELECT COUNT(*) 
FROM
(
    SELECT (SUM(wins) * 2) + SUM(ties) as Rank
    FROM scores
    GROUP BY userid
) tt
WHERE Rank > 35

友情链接