2021-05-10

销售分析

需求一：编写一个 SQL 查询，查询总销售额最高的销售者，如果有并列的，就都展示出来。

展示效果：

seller_id
1
3

Create table If Not Exists 45_Product (product_id int, product_name varchar(10), unit_price int);
Create table If Not Exists 45_Sales (seller_id int, product_id int,buyer_id int, sale_date date, quantity int, price int);
Truncate table 45_Product;
insert into 45_Product (product_id, product_name, unit_price) values (1, 'S8', 1000);
insert into 45_Product (product_id, product_name, unit_price) values (2, 'G4', 800);
insert into 45_Product (product_id, product_name, unit_price) values (3, 'iPhone', 1400);
Truncate table 45_Sales;
insert into 45_Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values (1, 1, 1,'2019-01-21', 2, 2000);
insert into 45_Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values (1, 2, 2,'2019-02-17', 1, 800);
insert into 45_Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values (2, 1, 3,'2019-06-02', 1, 800);
insert into 45_Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values (3, 3, 3,'2019-05-13', 2, 2800);

最终SQL:

select 
      seller_id 
from 
      45_Sales
group by 
      seller_id
having 
      sum(price) = (select
                          sum(price) as ye_ji
                    from  
                          45_Sales
                    group by
                          seller_id
                    order by 
                          ye_ji desc
                    limit 1
                   );