使用max()和sum() [英] using max() and sum()

问题描述

我需要找到"2009年收入最高的分支机构的名称".我的表格如下:

I need to find the 'name of the branch that has made the most money in 2009'. My tables are below:

Rental
(cid, copyid, outdate, returndate, cost)

Copy
(copyid, mid, bid)

Branch
(bid, bname, baddress)

我写了下面的代码，它输出所有分支的总和，但是我需要赚钱最多的分支的总和.我不确定如何在同一查询中加入max()和sum()函数.我正在使用Oracle 2007.

I have written the following code, and it outputs the sum of all branches, but I need the sum of the branch that made the most money. I am not sure how to join a max() and sum() function in the same query. I am using Oracle 2007.

输出分支名称和总和(我得到所有分支的摘要):

Output the branch name and sum (I get a summary of all branches with this):

SELECT bname, sum(cost) as Total
FROM rented R join copy C on R.copyid = C.copyid join branch B on C.bid = B.bid
WHERE outdate between '20090101' and '20091231'
GROUP BY bname;

输出总和的最大值(我没有得到我的分支名称):

Output the max of sum (I don't get my branch name with this):

SELECT sum(total_cost)
FROM (SELECT max(cost) as total_cost FROM rented WHERE outdate between '20090101' and '20091231') x;

如何将这两个合并在一起，以仅获得最大总和分支名称?

How can I merge these two together to get only the max sum branch name?

推荐答案

为什么不从您的第一个查询中获取第一行?

Why not just grab the top row from your first query?

   SELECT * FROM (
            SELECT bname, sum(cost) as Total
            FROM rented R join copy C on R.copyid = C.copyid join branch B on C.bid = B.bid
            WHERE outdate between '20090101' and '20091231' 
            GROUP BY bname
            ORDER BY sum(cost) desc) 
   WHERE ROWNUM=1;

然而，您可能会忘记的一件事是，两个分支机构可以并列以赚取最多的收入.如果要包括所有参加并列第一名的分支机构，则可以使用类似的内容(Oracle 9i或更高版本).

One thing you may be forgetting however, is that two branches could tie for making the most money. If you want to include all branches that participate in the tie for 1st place you might go with something like this (Oracle 9i or later).

    SELECT * FROM (
           SELECT bname, 
           sum(cost) as Total, 
           RANK() OVER (ORDER BY sum(cost) desc) "Rank"
           FROM rented R 
           join copy C on R.copyid = C.copyid 
           join branch B on C.bid = B.bid
           WHERE outdate between '20090101' and '20091231'
          GROUP BY bname) 
     WHERE Rank=1;

这篇关于使用max()和sum()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！