SQL 不是单组组函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1795198/
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
SQL not a single-group group function
提问by Tomek
When I run the following SQL statement:
当我运行以下 SQL 语句时：
SELECT MAX(SUM(TIME))
FROM downloads
GROUP BY SSN
It returns the maximum sum value of downloads by a customer, however if I try to find the social security number that that max value belongs to by adding it to the select statement:
它返回客户下载的最大总和值，但是，如果我尝试通过将其添加到 select 语句中来查找该最大值所属的社会保险号：
SELECT SSN, MAX(SUM(TIME))
FROM downloads
GROUP BY SSN
I get the following error:
我收到以下错误：
not a single-group group function
不是单组组功能
I do not understand why it is throwing this error. A google search came up with the following action:
我不明白为什么它会抛出这个错误。谷歌搜索提出了以下操作：
Drop either the group function or the individual column expression from the SELECT list or add a GROUP BY clause that includes all individual column expressions listed
从 SELECT 列表中删除 group 函数或单个列表达式，或者添加一个 GROUP BY 子句，其中包括列出的所有单个列表达式
From what I think this is saying - dropping the group function makes the sum value invalid - droping the individual column expression (SSN) will just give me the max sum - not sure about that third part.
从我认为这是说 - 删除组函数会使总和值无效 - 删除单个列表达式（SSN）只会给我最大总和 - 不确定第三部分。
Could anyone guide in the right direction?
任何人都可以指导正确的方向吗？
-Tomek
-托梅克
EDIT: TIME in this database refers to the number of times downloaded
编辑：此数据库中的时间是指下载的次数
采纳答案by fyjham
Well the problem simply-put is that the SUM(TIME) for a specific SSN on your query is a single value, so it's objecting to MAX as it makes no sense (The maximum of a single value is meaningless).
好吧，简单地说，问题是查询中特定 SSN 的 SUM(TIME) 是单个值，因此它反对 MAX，因为它没有意义（单个值的最大值毫无意义）。
Not sure what SQL database server you're using but I suspect you want a query more like this (Written with a MSSQL background - may need some translating to the sql server you're using):
不确定您使用的是什么 SQL 数据库服务器，但我怀疑您想要更像这样的查询（使用 MSSQL 背景编写 - 可能需要一些转换为您正在使用的 sql 服务器）：
SELECT TOP 1 SSN, SUM(TIME)
FROM downloads
GROUP BY SSN
ORDER BY 2 DESC
This will give you the SSN with the highest total time and the total time for it.
这将为您提供总时间最长的 SSN 及其总时间。
Edit - If you have multiple with an equal time and want them all you would use:
编辑 - 如果你有多个相同的时间并且想要它们，你会使用：
SELECT
SSN, SUM(TIME)
FROM downloads
GROUP BY SSN
HAVING SUM(TIME)=(SELECT MAX(SUM(TIME)) FROM downloads GROUP BY SSN))
回答by Egor Rogov
If you want downloads number for each customer, use:
如果您想要每个客户的下载数量，请使用：
select ssn
, sum(time)
from downloads
group by ssn
If you want just one record -- for a customer with highest number of downloads -- use:
如果您只需要一条记录——对于下载次数最多的客户——请使用：
select *
from (
select ssn
, sum(time)
from downloads
group by ssn
order by sum(time) desc
)
where rownum = 1
However if you want to see all customers with the same number of downloads, which share the highest position, use:
但是，如果您想查看下载次数相同且排名最高的所有客户，请使用：
select *
from (
select ssn
, sum(time)
, dense_rank() over (order by sum(time) desc) r
from downloads
group by ssn
)
where r = 1
回答by Khb
Maybe you find this simpler
也许你觉得这更简单
select * from (
select ssn, sum(time) from downloads
group by ssn
order by sum(time) desc
) where rownum <= 10 --top 10 downloaders
Regards
K
问候
K
