在Group By查询中列出Biggest_Purchase在Desc Order中

Question

以下是我的查询:

select MAX(o.amount)as Biggest_Purchase , c.cust_fname, c.cust_lname 
from orders o, customer c
where o.cust_num = c.cust_num
group by c.cust_fname, c.cust_lname
order by o.amount desc;

为什么我得到以下错误?:

留言8127,16层，状态1，线路5列"命令。amount"在ORDER BY子句中无效，因为它既不包含在聚合函数中，也不包含在GROUP BY子句中。

Answer 1

只能在order by的select子句中指定列。我想你的意思是:

select MAX(o.amount)as Biggest_Purchase , c.cust_fname, c.cust_lname 
from orders o, customer c
where o.cust_num = c.cust_num
group by c.cust_fname, c.cust_lname
order by Biggest_Purchase desc;

Answer 2

select MAX(o.amount)as Biggest_Purchase , c.cust_fname, c.cust_lname 
from orders o, customer c
where o.cust_num = c.cust_num
group by c.cust_fname, c.cust_lname
order by max(o.amount) desc;