题目描述

(通过次数18,546 | 提交次数24,227,通过率76.55%)

产品表:Product
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| product_id | int |
| product_name | varchar |
| unit_price | int |
+--------------+---------+
product_id 是这个表的主键.
该表的每一行显示每个产品的名称和价格。
销售表:Sales
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| seller_id | int |
| product_id | int |
| buyer_id | int |
| sale_date | date |
| quantity | int |
| price | int |
+------ ------+---------+
这个表没有主键,它可以有重复的行。
product_id 是 Product 表的外键。
该表的每一行包含关于一个销售的一些信息。
编写一个 SQL 查询,查询总销售额最高的销售者,如果有并列的,就都展示出来。
以 任意顺序 返回结果表。
查询结果格式如下所示。
示例 1:
输入:
Product 表:
+------------+--------------+------------+
| product_id | product_name | unit_price |
+------------+--------------+------------+
| 1 | S8 | 1000 |
| 2 | G4 | 800 |
| 3 | iPhone | 1400 |
+------------+--------------+------------+
Sales 表:
+-----------+------------+----------+------------+----------+-------+
| seller_id | product_id | buyer_id | sale_date | quantity | price |
+-----------+------------+----------+------------+----------+-------+
| 1 | 1 | 1 | 2019-01-21 | 2 | 2000 |
| 1 | 2 | 2 | 2019-02-17 | 1 | 800 |
| 2 | 2 | 3 | 2019-06-02 | 1 | 800 |
| 3 | 3 | 4 | 2019-05-13 | 2 | 2800 |
+-----------+------------+----------+------------+----------+-------+
输出:
+-------------+
| seller_id |
+-------------+
| 1 |
| 3 |
+-------------+
解释:Id 为 13 的销售者,销售总金额都为最高的 2800
来源:力扣(LeetCode)
链接:https://leetcode.cn/problems/sales-analysis-i
产品表:Product +--------------+---------+ | Column Name | Type | +--------------+---------+ | product_id | int | | product_name | varchar | | unit_price | int | +--------------+---------+ product_id 是这个表的主键. 该表的每一行显示每个产品的名称和价格。 销售表:Sales +-------------+---------+ | Column Name | Type | +-------------+---------+ | seller_id | int | | product_id | int | | buyer_id | int | | sale_date | date | | quantity | int | | price | int | +------ ------+---------+ 这个表没有主键,它可以有重复的行。 product_id 是 Product 表的外键。 该表的每一行包含关于一个销售的一些信息。 编写一个 SQL 查询,查询总销售额最高的销售者,如果有并列的,就都展示出来。 以 任意顺序 返回结果表。 查询结果格式如下所示。 示例 1: 输入: Product 表: +------------+--------------+------------+ | product_id | product_name | unit_price | +------------+--------------+------------+ | 1 | S8 | 1000 | | 2 | G4 | 800 | | 3 | iPhone | 1400 | +------------+--------------+------------+ Sales 表: +-----------+------------+----------+------------+----------+-------+ | seller_id | product_id | buyer_id | sale_date | quantity | price | +-----------+------------+----------+------------+----------+-------+ | 1 | 1 | 1 | 2019-01-21 | 2 | 2000 | | 1 | 2 | 2 | 2019-02-17 | 1 | 800 | | 2 | 2 | 3 | 2019-06-02 | 1 | 800 | | 3 | 3 | 4 | 2019-05-13 | 2 | 2800 | +-----------+------------+----------+------------+----------+-------+ 输出: +-------------+ | seller_id | +-------------+ | 1 | | 3 | +-------------+ 解释:Id 为 1 和 3 的销售者,销售总金额都为最高的 2800。 来源:力扣(LeetCode) 链接:https://leetcode.cn/problems/sales-analysis-i
产品表:Product
+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| product_id   | int     |
| product_name | varchar |
| unit_price   | int     |
+--------------+---------+
product_id 是这个表的主键.
该表的每一行显示每个产品的名称和价格。
销售表:Sales
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| seller_id   | int     |
| product_id  | int     |
| buyer_id    | int     |
| sale_date   | date    |
| quantity    | int     |
| price       | int     |
+------ ------+---------+
这个表没有主键,它可以有重复的行。
product_id 是 Product 表的外键。
该表的每一行包含关于一个销售的一些信息。
编写一个 SQL 查询,查询总销售额最高的销售者,如果有并列的,就都展示出来。
以 任意顺序 返回结果表。
查询结果格式如下所示。
示例 1:
输入:
Product 表:
+------------+--------------+------------+
| product_id | product_name | unit_price |
+------------+--------------+------------+
| 1          | S8           | 1000       |
| 2          | G4           | 800        |
| 3          | iPhone       | 1400       |
+------------+--------------+------------+
Sales 表:
+-----------+------------+----------+------------+----------+-------+
| seller_id | product_id | buyer_id | sale_date  | quantity | price |
+-----------+------------+----------+------------+----------+-------+
| 1         | 1          | 1        | 2019-01-21 | 2        | 2000  |
| 1         | 2          | 2        | 2019-02-17 | 1        | 800   |
| 2         | 2          | 3        | 2019-06-02 | 1        | 800   |
| 3         | 3          | 4        | 2019-05-13 | 2        | 2800  |
+-----------+------------+----------+------------+----------+-------+
输出:
+-------------+
| seller_id   |
+-------------+
| 1           |
| 3           |
+-------------+
解释:Id 为 1 和 3 的销售者,销售总金额都为最高的 2800。
来源:力扣(LeetCode)
链接:https://leetcode.cn/problems/sales-analysis-i
//测试数据
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);
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');
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');
//测试数据 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); 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'); 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');
//测试数据
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);
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');
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');

解题思路

Sales表中保存了所有产品的销售信息。包括销售人员ID、销售额。

题目要求:查询销售额最高的销售人员ID。

首先,需要查询出每个销售人员的销售额。直接对Sales表分组统计即可。

然后,获取最大的销售额。

最后,再查询出最大销售额的销售人员ID。

注意,这里不可以直接对销售额倒序排序取第一名,因为无法确定是否有多人并列第一名。当然,使用开窗函数也是一个比较好的选择。

参考SQL

未特别说明的情况下,参考SQL为基于MySQL8.0实现。
with
tmp as (
select
seller_id,
sum(price) sum_price
from Sales
group by seller_id
)
select
seller_id
from tmp
where sum_price in (
select
max(sum_price)
from tmp
);
with tmp as ( select seller_id, sum(price) sum_price from Sales group by seller_id ) select seller_id from tmp where sum_price in ( select max(sum_price) from tmp );
with
tmp as (
    select
        seller_id,
        sum(price) sum_price
    from Sales
    group by seller_id
)
select
    seller_id
from tmp
where sum_price in (
    select
        max(sum_price)
    from tmp
);
picture loss