根据两个表的总销售额获取前5名数据

lb3vh1jj  于 2021-06-17  发布在  Mysql
关注(0)|答案(1)|浏览(254)

我有“销售”表和类似于此方案的数据:

name | 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

用户表如下:

User    |  Pict
user a    user_a.jpg
user b    user_a.jpg
user c    user_a.jpg

如何根据每个月的总销售额获得前5名用户,我尝试过使用这样的查询,但总是显示不正确:

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

结果应该是:

user_a.jpg | User a | 300
user_b.jpg | User b | 370
user_c.jpg | User c | 360

所以订单必须是:b,c,a,基于最高总销售额。
但输出总是这样:

user_a.jpg | User a | 300
user_a.jpg | User b | 370
user_a.jpg | User c | 360

第一列始终为用户\u a.jpg

4uqofj5v

4uqofj5v1#

你已经成功了一半。将您的查询与users表联接,如下所示:

SELECT users.user, users.pict, total_sales
FROM (
    SELECT name, SUM(Amount) AS total_sales
    FROM sales
    WHERE Month BETWEEN 1 AND 12
    GROUP BY name
    ORDER BY total_sales
    LIMIT 5
) AS user_sales
INNER JOIN users ON user_sales.name = users.user
ORDER BY total_sales DESC

你好像把列名弄混了;您必须相应地修改上述查询。

相关问题