SQL最大值和总和

Question

下面是我正在使用的查询：

SELECT 
   County,
   Code,
   Sum(PaidAmount) AS TotalPaid
FROM
   Counties
GROUP BY
   County,
   Code

它返回集合：

County     Code         TotalPaid
Brown      99           210.21
Lyon       73           322.22
Lyon       88           533.22
Lincoln    22           223.21

我要找的是一个查询，它将返回显示每个县的县和最大总支付代码的行。我需要的结果集的例子如下所示(注意，Lyon，73被删除，因为Lyon，88的TotalPayed金额更高(：

County     Code         TotalPaid
Brown      99           210.21
Lyon       88           533.22
Lincoln    22           223.21

Answer 1

我没能测试这个，但RANK应该解决这个问题：

SELECT x.County, x.Code x.TotalPaid
    ,RANK() OVER 
    (PARTITION BY x.County ORDER BY x.TotalPaid DESC) AS 'RANK'
FROM
(SELECT 
   County,
   Code,
   Sum(PaidAmount) AS TotalPaid
FROM
   Counties
GROUP BY
   County,
   Code) x
WHERE Rank = 1

Answer 2

我认为您需要做以下操作。我还没来得及复习我写的东西就被叫走了，但希望它能给你足够的指导。有些RDBMS不允许"where country，TotalPayd=select value，value"构造，但您可以绕过这个

select 
   County,
   Code,
   TotalPaid
from (SELECT 
       County,
       Code,
       Sum(PaidAmount) AS TotalPaid
     FROM
       Counties
     GROUP BY
       County,
       Code ) tbl
 where County, TotalPaid = (select County, 
                                 max(TotalPaid)
                             FROM
                                 Counties
                             GROUP BY
                                 County,
                                 Code ) tbl2

Answer 3

   SELECT 
   c.County,
   c.Code,
   Sum(c.PaidAmount) AS TotalPaid
   FROM
   Counties c
   WHERE
   c.Code in (select max(c2.code) from counties c2 where c2.county = c.county)
   GROUP BY
   c.County,
   c.Code

这个应该可以工作，尽管我还没有测试

Answer 4

您必须使用窗口函数才能做到这一点。虽然你想要的东西很容易用英语表达，但不幸的是，它不容易用SQL表达。这应该做你需要的：

select
    County, Code, TotalPaid
from
(
SELECT 
   County,
   Code,
   sum(PaidAmount) AS TotalPaid
FROM
   Counties
GROUP BY
   County, Code
) source
where (row_number() over (partition by County order by TotalPaid desc)) = 1

Answer 5

这里有一个更新的解决方案：

select c1.county, c1.code, c1.paidAmount 
from counties c1
inner join (
  select county, max(paidAmount) paidAmount 
  from counties 
  group by county) c2 
on c1.county=c2.county and c1.paidAmount=c2.paidAmount;

请注意，如果某个县有多个最高付款额，这将返回共享该最高付款额的所有行。