找出10个产品销售额最高的用户

我有两个类用户和销售。我正试图编写一个请求，寻找10人与最大的销售额

任务:

找到10个用户

产品销售额最高

找出最大的销售额

select sum_amount_total.*
from (select sum(amount) as total_amount
    from
        User as u
            join Sell as a on u.id = a.seller
    group by amount
) as amountSum
    join (select sum(amount) as sum_amount
    from
        Sell
    group by
        seller
    order by
        sum_amount desc
) as sum_amount_total on amountSum.total_amount > sum_amount_total.sum_amount limit 2;

创建表脚本

Create table User (id int, name varchar(255));
Truncate table User;
insert into User (id, name) values ('1', 'Joe');
insert into User (id, name) values ('2', 'Henry');
Create table Sell (id int, seller INT, buyer INT, amount FLOAT);
Truncate table Sell;
insert into Sell (id, seller, buyer, amount) values (1, 1, 2, 134444.7);
insert into Sell (id, seller, buyer, amount) values (2, 2, 1, 1241.7);
insert into Sell (id, seller, buyer, amount) values (3, 1, 2, 123.4);
insert into Sell (id, seller, buyer, amount) values (4, 1, 2, 1000000.0);

问题:

如何简化SQL脚本？

我不明白为什么我的请求不能正常工作。例子:

select * from Sell;

结果:

id|seller|buyer|amount
1, 1,     2,    134445
2, 1,     3,    1241.7
3, 2,     1,    123.4
4, 3,     3,    123.4
5, 3,     2,    134445
6, 2,     3,    1241.7
7, 2,     1,    123.4
8, 1,     3,    123.4
9, 1,     3,    10000
10,1,     3,    1000000

我查询(1)找到最大金额:

sum_amount
134568.10312652588
134568.10312652588
1488.4999542236328
1488.4999542236328
1488.4999542236328
1488.4999542236328

我的请求没有正确计算出最大的金额

什么需要更改的sql脚本工作，并添加一个条件，以搜索最大的销售额？

提前感谢您的回答！

###简化你的sql脚本表User :

insert into User (id, name) values ('1', 'Joe'), ('2', 'Henry');

而不是

insert into User (id, name) values ('1', 'Joe');
insert into User (id, name) values ('2', 'Henry');

对于表Sell也一样:

insert into Sell (id, seller, buyer, amount) values (1, 1, 2, 134444.7), (2, 2, 1, 1241.7), (3, 1, 2, 123.4), (4, 1, 2, 1000000.0);

然后:

WITH list AS
(
select seller, sum(amount) as total_amount
  from Sell
 group by seller
 order by total_amount DESC
 limit 10
)
select u.*, l.total_amount
  from list AS l
 inner join user AS u
    on u.id = l.seller

最后找到10个产品销量最多的用户:

WITH list AS
(
select seller, coun(*) as total_count
  from Sell
 group by seller
 order by total_count DESC
 limit 10
)
select u.*, l.total_count
  from list AS l
 inner join user AS u
    on u.id = l.seller

# # #脚本创建表

Create table User(id int, name varchar(255));
Truncate table Users;
insert into Users (id, name) values ('1', 'Joe');
insert into Users (id, name) values ('2', 'Henry');
insert into Users (id, name) values ('3', 'Liza');
Create table Sell (id int, seller INT, buyer INT, amount FLOAT);
Truncate table Sell;
insert into Sell (id, seller, buyer, amount) values (1, 1, 2, 134444.7);
insert into Sell (id, seller, buyer, amount) values (2, 2, 1, 1241.7);
insert into Sell (id, seller, buyer, amount) values (3, 1, 2, 123.4);
insert into Sell (id, seller, buyer, amount) values (4, 1, 2, 1000000.0);
insert into Sell (id, seller, buyer, amount) values (5, 3,     2,    134445);
insert into Sell (id, seller, buyer, amount) values (6, 2,     3,    1241.7);
insert into Sell (id, seller, buyer, amount) values (7, 2,     1,    123.4);
insert into Sell (id, seller, buyer, amount) values (8, 3,     3,    123.4);
insert into Sell (id, seller, buyer, amount) values (9, 1,     3,    10000);
insert into Sell (id, seller, buyer, amount) values (10,1,     3,    1000000);

查询以获得销售计数

SELECT name,count(Sell.id) as nsales,sum(amount),rank() 
       over(order by count(Sell.id) desc) 
   from Sell 
       inner join Users 
   on(seller=Users.id) group by name
   limit 10;
name  | nsales |    sum    | rank
-------+--------+-----------+------
 Joe   |      5 | 2144568.1 |    1
 Henry |      3 |    2606.8 |    2
 Liza  |      2 |  134568.4 |    3

并查询得到顶部的数量

SELECT name,count(Sell.id) as nsales,sum(amount),rank()
       over(order by sum(amount) desc) 
    from Sell 
       inner join Users 
    on(seller=Users.id) group by name 
    limit 10;
 name  | nsales |    sum    | rank
-------+--------+-----------+------
 Joe   |      5 | 2144568.1 |    1
 Liza  |      2 |  134568.4 |    2
 Henry |      3 |    2606.8 |    3

没有排名的结果相同

SELECT name,count(Sell.id) as nsales,sum(amount) amount_sum 
    from Sell inner join Users on(seller=Users.id) 
group by name 
order by amount_sum desc
limit 10;
 name  | nsales | amount_sum
-------+--------+------------
 Joe   |      5 |  2144568.1
 Liza  |      2 |   134568.4
 Henry |      3 |     2606.8
SELECT name,count(Sell.id) as nsales,sum(amount) amount_sum 
    from Sell inner join Users on(seller=Users.id) 
group by name 
order by nsales desc 
limit 10;
 name  | nsales | amount_sum
-------+--------+------------
 Joe   |      5 |  2144568.1
 Henry |      3 |     2606.8
 Liza  |      2 |   134568.4