查找最高金额，尽管有多个达到相同金额

nnvyjq4y 于 2021-07-29 发布在 Java

关注(0)|答案(1)|浏览(348)

我想为每个国家找到最好的客户尽管有一个国家有两个相同数量的客户，我希望他们都出现。

select customerid,firstname,lastname,country, max(total_amt)
from (select invoice.customerid, customer.firstname,lastname, 
sum(invoice.total)total_amt,customer.country
  from invoice
   join customer
   on customer.customerid= invoice.customerid
   group by invoice.customerid,customer.country)t2
group by  country;

sql Max

来源：https://stackoverflow.com/questions/63122053/find-the-highest-amount-although-there-are-more-than-one-achieving-the-same-amou

1条答案

按热度按时间

chhkpiq41#

使用窗口功能：

select c.*
from (select c.country, c.customerid, c.firstname c.lastname, sum(i.total) as total,
             dense_rank() over (partition by c.country order by sum(i.total) desc) as seqnum
      from customer c join
           invoice i
           on c.customerid = i.customerid
     ) c
where seqnum = 1;

注意，我还引入了窗口函数，因此查询更易于编写和读取。

赞(0）回复(0）举报 2021-07-29

我来回答

查找最高金额，尽管有多个达到相同金额

1条答案

相关问题

热门标签

最新问答