SQL-案例：销售金额总和最大的=销售

12级龙卷风

已于 2023-04-29 18:57:20 修改

阅读量986

点赞数

文章标签：数据库 mysql

于 2023-04-29 18:52:03 首次发布

本文链接：https://blog.csdn.net/dameijiangshan/article/details/130442775

版权

该文提供了一个SQL查询示例，用于查找总销售额最高的销售者。如果存在多个销售者的销售额相同且为最高，则都会被显示在结果中。查询首先通过内连接产品和销售表，然后计算每个销售者的总销售额，最后使用子查询找出最大销售额并返回相应的销售者信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

编写一个 SQL 查询，查询总销售额最高的销售者，如果有并列的，就都展示出来，以任意顺序返回结果表。

drop table Product;
Create table If Not Exists Product (product_id int, product_name varchar(10), unit_price int);
Create table If Not Exists Sales (seller_id int, product_id int, buyer_id int, sale_date date, quantity int, price int);

Truncate table Product;
insert into Product (product_id, product_name, unit_price) values ('1', 'S8', '1000');
insert into Product (product_id, product_name, unit_price) values ('2', 'G4', '800');
insert into Product (product_id, product_name, unit_price) values ('3', 'iPhone', '1400');

Truncate table Sales;
insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('1', '1', '1', '2019-01-21', '2', '2000');
insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('1', '2', '2', '2019-02-17', '1', '800');
insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('2', '2', '3', '2019-06-02', '1', '800');
insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('3', '3', '4', '2019-05-13', '2', '2800');

解题思路：


-- 1)判断用什么连接？需要显示需求中的product_name商品名字，销售商品价格price，销售数量quantity，销售人员seller_id
select p.product_name,S.quantity,S.seller_id, S.price from Product p inner join Sales S on p.product_id = S.product_id;
-- 2)在基础上求商品的总售价和：售价*数量
select
       p.product_name,
       S.quantity,
       S.seller_id,
       S.price,
       (S.price * s.quantity) as sum_num
from Product p inner join Sales S on p.product_id = S.product_id;
-- 3）在基础上最大的售价和
select
       max(S.price * s.quantity) num
from Product p inner join Sales S on p.product_id = S.product_id ;
-- 4）添加限制条件，求最大和售价和对应的产品和销售,当有并列的最大值，都显示出来
select
       S.seller_id,
       p.product_name,
       (S.price * s.quantity) as sum_num

from Product p inner join Sales S on p.product_id = S.product_id
where (S.price * s.quantity)=(select
       max(S.price * s.quantity) num
from Product p inner join Sales S on p.product_id = S.product_id);

可修改数值：进行验算

Truncate table Sales;
insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('1', '1', '1', '2019-01-21', '2', '2000');
insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('1', '2', '2', '2019-02-17', '1', '800');
insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('2', '2', '3', '2019-06-02', '1', '800');
insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('3', '3', '4', '2019-05-13', '2', '2000');

验算：并列是否显示
-- 4）添加限制条件，求最大和售价和对应的产品和销售,当有并列的最大值，都显示出来
select
       S.seller_id,
       p.product_name,
       (S.price * s.quantity) as sum_num

from Product p inner join Sales S on p.product_id = S.product_id
where (S.price * s.quantity)=(select
       max(S.price * s.quantity) num
from Product p inner join Sales S on p.product_id = S.product_id);