我有兩個類，用戶和銷售。我正在嘗試撰寫一個請求，以查找銷售額最大的 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 腳本作業并添加條件來搜索最大銷售計數？預先感謝您的回答！
uj5u.com熱心網友回復：
簡化表的 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);
然后找到產品銷售額最高的 10 個用戶：
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
uj5u.com熱心網友回復：
創建表腳本
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/356356.html
標籤：数据库 PostgreSQL