转自:https://leetcode-cn.com/problems/sales-analysis-i/ 【侵删】
产品表:Product
product_id 是这个表的主键.
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| product_id | int |
| product_name | varchar |
| unit_price | int |
+--------------+---------+
销售表:Sales
这个表没有主键,它可以有重复的行.
product_id 是 Product 表的外键.
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| seller_id | int |
| product_id | int |
| buyer_id | int |
| sale_date | date |
| quantity | int |
| price | int |
+------ ------+---------+
编写一个 SQL 查询,查询总销售额最高的销售者,如果有并列的,就都展示出来。
查询结果格式如下所示:
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 |
+-----------+------------+----------+------------+----------+-------+
Result 表:
+-------------+
| seller_id |
+-------------+
| 1 |
| 3 |
+-------------+
Id 为 1 和 3 的销售者,销售总金额都为最高的 2800。
练习思路:
1. 使用窗口函数rank() over() 对每个销售人员的销售总额(这里用到了分组)进行排序
select seller_id,rank() over(order by sum(price) desc) my_rank from sales group by seller_id;
+-----------+------------+
| seller_id | my_rank |
+-----------+------------+
| 1 | 1 |
| 3 | 1 |
| 2 | 2 |
+-----------+------------+
2. 临时表创建完成之后,对该临时表进行一个查询(查询临时表中的排名第一的【注意:rank 函数是可以多个第一的】)
select t.seller_id from (select seller_id,rank() over(order by sum(price) desc) my_rank from sales group by seller_id) t where my_rank=1;
+-------------+
| seller_id |
+-------------+
| 1 |
| 3 |
+-------------+
【如果有其他更好的解法,我们可以一起坐下来讨论啊(不要喷我~)】