mysql - 如何在 MySQL 查询中找到最大总和的关联名称？

Question

我这里有这个架构，我需要找到订单总额最高的客户的姓名。我在这里有一个 SQL 查询：

SELECT Name
FROM (SELECT Name, SUM(Amount) AS Total
      FROM customer JOIN orders ON cust_id = ID
      GROUP BY Name) AS Totals
WHERE Total = (SELECT MAX(Total)
                      FROM (SELECT Name, SUM(Amount) AS Total
                            FROM customer JOIN orders ON cust_id = ID
                            GROUP BY Name) AS X);

但这非常低效，因为它两次创建同一个表。有没有更有效的方法来获取名称？

标签： mysqlsqlselectsql-order-bygreatest-n-per-group

Answer 1

如果您想要总安装量最大的客户，那么您可以加入、订购和限制：

select c.name
from customer c
inner join orders o on o.cust_id = c.id
group by c.id, c.name
order by sum(o.amount) desc 
limit 1

请注意，这不处理可能的顶部关系。为此，您需要更多代码。您通常会使用having子句进行过滤，而不是排序：

select c.name
from customer c
inner join orders o on o.cust_id = c.id
group by c.id, c.name
having sum(o.amount) = (
    select sum(o1.amount)
    from orders o1
    group by cust_id
    order by sum(o1.amount) desc
    limit 1
)

最后：如果您正在运行 MySQL 8.0，则使用窗口函数更简单rank()：

select name
from (
    select c.name, rank() over(order by sum(o.amount) desc) rn
    from customer c
    inner join orders o on o.cust_id = c.id
    group by c.id, c.name
) t
where rn = 1