SQL 在 Oracle 中对 SUM() 聚合函数使用 Max()
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8367729/
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
Using Max() on SUM() Aggregate function in Oracle
提问by user1078958
I am trying to write a query in Oracle which will return both the pub_id and the maximum total revenue from a titles table which lists pub_id, sales, price. I can get either a listing with pub_id and total revenues for each pub_id with
我正在尝试在 Oracle 中编写一个查询，该查询将从列出 pub_id、销售额、价格的标题表中返回 pub_id 和最大总收入。我可以获得带有 pub_id 的列表和每个 pub_id 的总收入
SELECT PUB_ID, SUM(SALES*PRICE) as TotalRevenue FROM TITLES GROUP BY PUB_ID;
Or I can get just the MAX(Sales*Price) with
或者我可以只得到 MAX(Sales*Price)
SELECT MAX(SUM(sales*price)) FROM titles GROUP BY pub_id;
Any ideas how can I get the pub_id out with the maximum of the total revenue?
任何想法如何以最大总收入获得 pub_id ？
回答by Greg Reynolds
You can use the rank function like this
您可以像这样使用排名函数
select * from
(
select a.*,rank() over (order by sum_sales desc) r from
(
select pub_id,sum(sales*price) sum_sales from titles group by pub_id
) a
)
where r = 1;
回答by bonsvr
That's simple with the powerful ORACLE ANALYTICAL FUNCTIONS
这很简单，功能强大 ORACLE ANALYTICAL FUNCTIONS
Below will give the max revenues for each pub_id.
下面将给出每个pub_id.
select pub_id,REV from
(
select pub_id, (sales*price) as REV,
max(sales*price) over (partition by pub_id order by 1) as MAX
from titles
)
where REV=MAX
If you want to determine the pub_idwith the maximum revenue:
如果要确定pub_id收入最高的 ：
select * from
(
select pub_id,REV from
(
select pub_id, (sales*price) as REV,
max(sales*price) over (partition by pub_id order by 1) as MAX
from titles
)
where REV=MAX order by MAX desc
)
where rownum<2
回答by Alessandro Rossi
There's no really need for analytic functions in this case. The best option would be to group two times one for the sum() and the following time for the max() with the dense_rankoption.
在这种情况下，真的不需要解析函数。最好的选择是将 sum() 分组两次，将 max() 的下一次分组为dense_rank选项。
select max(pub_id) keep (dense_rank last order by TotalRevenue)
from (
SELECT PUB_ID, SUM(SALES*PRICE) as TotalRevenue
FROM TITLES
GROUP BY PUB_ID
)
回答by SalahTriki
SELECT PUB_ID, SUM(SALES*PRICE) as TotalRevenue
FROM TITLES
GROUP BY PUB_ID
HAVING SUM(SALES*PRICE) = ( SELECT MAX(SUM(SALES*PRICE))
FROM TITLES
GROUP BY PUB_ID );
