如何计算 SQL 查询中已经使用过计数、分组依据和具有的行数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/245765/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do you count the rows in a SQL query which already used count, group by, and having before?
提问by danmine
For example, using the answer for this question:
例如，使用这个问题的答案：
How to select all users who made more than 10 submissions"How to select all users who made more than 10 submissions."
如何选择提交次数超过 10 次的所有用户 “如何选择提交次数超过 10 次的所有用户”。
select userId
from submission
group by userId
having count(submissionGuid) > 10
Let's say now I want to know many rows this sql statement outputted. How scalable is the solution for counting the rows of counting the rows?
假设现在我想知道这个 sql 语句输出的多行。计算行数的解决方案的可扩展性如何？
回答by user12861
Slight error in previously posted example, need an alias for a table name for the subquery:
先前发布的示例中存在轻微错误，需要子查询的表名的别名：
select count(*) from
(select userId
from submission
group by userId
having count(submissionGuid) > 10) t
I'm not sure about scalability, but this is the solution. If this isn't scaling well enough for you, you need to consider major design changes, like perhaps tracking those who submitted more than 10 submissions in a separate table that you update through applications that populate the submissions. Or many other possible solutions.
我不确定可扩展性，但这是解决方案。如果这对您来说不能很好地扩展，您需要考虑重大的设计更改，例如可能在一个单独的表中跟踪那些提交超过 10 个提交的人，您可以通过填充提交的应用程序更新该表。或许多其他可能的解决方案。
回答by BQ.
Nested queries:
嵌套查询：
select count(*) from
(select userId
from submission
group by userId
having count(submissionGuid) > 10) n
Edited to incorporate mbrierst's comment about needing an alias (the "n" at the end) for the nested subquery. Oracle does not require this, but SQL Server does. Feel free to add a comment regarding usage on other database platforms.
编辑以合并 mbrierst 关于嵌套子查询需要别名（末尾的“n”）的评论。Oracle 不需要这个，但 SQL Server 需要。请随意添加有关在其他数据库平台上使用的评论。
回答by Dave Neeley
In SQL Server you could do
在 SQL Server 中你可以做
select @@ROWCOUNT
immediately following the query you posted.
紧接着您发布的查询。
回答by Apple Yellow
You can try way this:
I hope to help you.
select top(1) row_number() over(partition by count(userId) order by count(userId)) as RowNumber
from submission
group by userId
having count(submissionGuid) > 10
order by userId desc
你可以试试这样：希望能帮到你。
select top(1) row_number() over(partition by count(userId) order by count(userId)) as RowNumber
from submission
group by userId
having count(submissionGuid) > 10
order by userId desc
