如何修改SQL实现按客户消费总金额从高到低排序展示

修正调整后的SQL语句 #

SELECT customers.first_name, SUM(orders.price) AS total_consumption
FROM customers 
INNER JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.id, customers.first_name
HAVING SUM(orders.price) > 100
ORDER BY total_consumption DESC;

改动说明 #

• 修复了原SQL的笔误：内连接条件中customer.id修正为customers.id，和表名匹配避免执行报错
• SELECT子句新增总消费金额字段SUM(orders.price) AS total_consumption，可直接查看每个客户的消费总额，不需要展示的话可以删除这部分，不影响排序逻辑
• GROUP BY子句补充了customers.id，避免同名客户被错误合并统计，同时符合严格SQL模式的分组校验规则
• 新增ORDER BY total_consumption DESC实现按总消费金额从高到低排序，需要升序排序时将DESC替换为ASC即可

如果不需要在结果中展示总消费金额，也可以使用简化版本：

SELECT customers.first_name
FROM customers 
INNER JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.id, customers.first_name
HAVING SUM(orders.price) > 100
ORDER BY SUM(orders.price) DESC;

内容的提问来源于stack exchange，提问作者user15040527