mysql - 如何显示最高订单数 i,e 在表(tcount)中与客户名称(名称)

Question

我创建了 2 个表:customers (table1) 和orders (table2)

在表 1 中，我创建了 columns(id,name)，在表 2 中，我创建了 columns(oid,customer_id,amount)。

接下来，我使用 sum() 函数来获取特定客户的总金额，并使用 count() 来获取特定客户订购的订单总数和我分别使用group by显示。

现在我不知道如何查找具有特定客户名称的最高订单。

这是我尝试过的:

select max(name) as aaa,max(tcount) as highorders
from
   ( SELECT name,sum(orderss.amount) AS tamount,count(orderss.oid) as tcount
     FROM customers
     INNER JOIN orderss ON customers.id=orderss.customer_id group by name
     having tamount > 50 and tcount >= 2
   ) as ho

Answer 1

使用排序方式

select max(name) as aaa,max(tcount) as highorders
from
   ( SELECT name,sum(orderss.amount) AS tamount,count(orderss.oid) as tcount
     FROM customers
     INNER JOIN orderss ON customers.id=orderss.customer_id group by name
     having tamount > 50 and tcount >= 2
   ) as ho
order by field_name DESC

(将 DESC 替换为 ASC 即可得到升序排列