database - 查找产品销售额总和最高的 10 个用户

Question

我有两个类，用户和销售。我正在尝试编写一个请求以查找销售额最大的 10 个人

任务：找到10个用户

1. 产品销售额最高的
2. 找到最大的销售额

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);

问题：

1. 如何简化sql脚本？
2. 我不明白为什么我的请求无法正常工作。例子：

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 脚本才能工作并添加条件以搜索最大销售额？提前感谢您的回答！

标签： databasepostgresql

Answer 1

创建表脚本

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