使用 max() 和 sum()

Question

我需要找到“2009 年赚最多钱的分行名称”。我的表格如下：

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

Copy
(copyid, mid, bid)

Branch
(bid, bname, baddress)

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

输出分支名称和总和（我用这个得到所有分支的摘要）：

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;

输出总和的最大值（我没有用这个得到我的分支名称）：

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

如何将这两个合并在一起只获取最大总和分支名称？

Answer 1

有很多方法。例如，保留原始查询，您可以按总和对数据进行排序，然后只取第一行：

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; 

Answer 2

为什么不直接从第一个查询中获取顶行呢？

   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 或更高版本）。

    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;