Group By Order By 返回错误的列 - mysql

使用MySQL进行数据查询和排序是非常常见的操作。通常，我们可以使用GROUP BY子句将数据按照某个列进行分组，然后使用ORDER BY子句对每个分组进行排序。然而，有时候我们可能会遇到一个问题：当我们在GROUP BY子句中使用了某个列，而在ORDER BY子句中使用了另一个列时，返回的结果不正确。本文将探讨这个问题，并提供解决方法。

+-----------+--------------+--------------+---------------+
| order_id  | customer_name| order_date   | order_amount  |
+-----------+--------------+--------------+---------------+
| 1         | John         | 2022-01-01   | 100           |
| 2         | John         | 2022-01-02   | 200           |
| 3         | Lisa         | 2022-01-03   | 150           |
| 4         | Lisa         | 2022-01-04   | 300           |
| 5         | Lisa         | 2022-01-05   | 250           |
+-----------+--------------+--------------+---------------+

sql
SELECT customer_name, SUM(order_amount) AS total_amount
FROM orders
GROUP BY customer_name
ORDER BY total_amount DESC;

sql
SELECT customer_name, total_amount
FROM (
  SELECT customer_name, SUM(order_amount) AS total_amount
  FROM orders
  GROUP BY customer_name
) AS subquery
ORDER BY total_amount DESC;