根据销售额获取前10名用户的数据

Question

我有表"sales"和类似以下方案的数据：

User    | Amount| Month
user a      100     1
user b      240     1
user c      120     1
user a      200     2
user b      130     2
user c      240     2

如何根据每月的总销售额获得TOP5用户，我尝试过使用这样的查询，但总是显示错误

SELECT TOP10 USER, 
SUM(amount) amount
FROM   sales LIMIT 10
WHERE  MONTH BETWEEN 1 AND 12
GROUP BY sales
ORDER BY 2 DESC

结果应该是：

User a | 300
User b | 370
User c | 360

所以订单必须是：B，C，A

Answer 1

您可以在User上执行GROUP BY，并使用SUM(amount)为每个用户获取total_sales。现在，只需按降序对total_sales设置的结果进行排序，即可首先获得最高销售额。

我们可以使用LIMIT 10，以防您只想获得前10名。

SELECT User, 
SUM(amount) AS total_sales 
FROM sales 
WHERE MONTH BETWEEN 1 AND 12
GROUP BY User
ORDER BY total_sales DESC
LIMIT 10

Answer 2

您可以在下面尝试

SELECT USER, SUM(amount) amount
FROM   sales 
WHERE  MONTH BETWEEN 1 AND 12
GROUP BY USER
ORDER BY amount DESC