计算的记录数做一个GROUP BY在SQL后

本文介绍了计算的记录数做一个GROUP BY在SQL后的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

有没有办法在SQL来计算给定的记录集，已经具备了 GROUP BY 适用于它的记录数？

我有一个包含了胜利，领带和损失在系列游戏的所有用户进行每周汇总表。它看起来有点像这样的：

 用户ID weekid胜损失关系
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

我要确定给定用户的行列。我会通过计算用户的数量有更好的组合胜，领带和损失比用户做到这一点。如果我想确定用户＃3，例如军衔，我可以用此查询，但它只是让我半路上有：

  SELECT COUNT（用户ID）
从分数
GROUP BY用户标识
具有：（SUM（胜）* 2）+ SUM（关系）GT; 35

结果是这样的：

  COUNT（用户ID）
3
3

这是什么给了我是包含2记录的记录集（这意味着他排名第三）。我真的希望是记录（2），而不是记录本身只是数量。我唯一能想到的是转储记录集到一个临时表，并执行 COUNT（）有，但那是pretty的昂贵。或者，我可以用手设置回C＃中的数据，并做记录计数出现，但如果给定的用户有一个可怕的记录，记录我需要发送的数量变得相当庞大。

我希望有一个相对廉价的方式来做到这一点。

解决方案

  SELECT COUNT（*）
从
（
    SELECT（SUM（胜）* 2）+ SUM（关系）的排名
    从分数
    GROUP BY用户标识
）TT
WHERE排名＆GT; 35

Is there a way in SQL to count the number of records in a given record set that already has had a GROUP BY applied to it?

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

The result would look like this:

COUNT(userid)
3
3

What this gives me is a record set containing 2 records (meaning he ranks third). What I would really like is just the number of records (2) rather than the records themselves. The only thing I can think of is to dump the record set to a temporary table and do the COUNT() there, but that's pretty expensive. Or I can hand the data set back to C# and do the record count there, but if the given user has a terrible record, the number of records I need to send gets quite huge.

I'm hoping there's a relatively cheap way to do this.

解决方案

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

这篇关于计算的记录数做一个GROUP BY在SQL后的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！