SQL求最大值

发布于04月06日

我有两张桌子:

Customer table

CustomerId	FirstName	LastName	Country
1	Luís	Gonçalves	Brazil
2	Leonie	Köhler	Germany
3	François	Tremblay	Canada
4	Bjørn	Hansen	Norway
52	Emma	Jones	United Kingdom
53	Phil	Hughes	United Kingdom

Invoice table

InvoiceId	CustomerId	Total
1	2	1.98
2	4	3.96
3	8	5.94
140	52	23.76
369	52	13.86
283	53	28.71
109	53	8.91

我必须写一个查询，返回国家、顶级客户以及他们花了多少钱.对于共享最高消费金额的国家，请提供所有花费此金额的客户.

我写了一个这样的问题:

SELECT c.CustomerId, c.FirstName, c.LastName, c.Country, SUM(i.Total) AS TotalSpent
FROM Customer c
JOIN Invoice i
ON c.CustomerId = i.CustomerId
GROUP BY c.FirstName, c.LastName
HAVING i.Total >= MAX(i.Total)
ORDER BY c.Country;

查询未找到最大值，但返回所有可用值.

感谢您的帮助.非常感谢.

推荐答案

您没有与我们共享数据库.

select CustomerId
       , FirstName
       , LastName
       , Country
       , max(tot)
from ( select sum(i1.Total) as tot
              , i1.CustomerId
              , c1.Country
              , c1.FirstName
              , c1.LastName
       FROM Customer c1
       JOIN Invoice i1
       ON c1.CustomerId = i1.CustomerId
       group by i1.CustomerId) TAB
group by Country

DEMO

在OP的 comments 之后，我编辑了代码:

select c.CustomerId, c.FirstName, c.LastName, c.Country, sum(Total)
from Customer c
JOIN Invoice i ON c.CustomerId = i.CustomerId
group by country, c.CustomerId
having sum(Total) in (select  max(tot) as tot2
                      from (select sum(i1.Total) as tot
                                   , country
                            FROM Customer c1
                            JOIN Invoice i1
                            ON c1.CustomerId = i1.CustomerId
                            group by i1.CustomerId) TAB
                      group by country)

DEMO2