编写一个 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。
来源:力扣(LeetCode)
链接:https://leetcode-cn.com/problems/sales-analysis-i
著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。
#法1,开窗,但是要注意由于要根据聚合函数count(employee_id)来排序,所以此次开创不能用partition by来分组,需要用group by分组
select a.seller_id
from(
select seller_id,dense_rank() over(order by sum(price) desc) r
from sales
group by seller_id) a
where r=1
#法2
select seller_id
from sales
group by seller_id
having sum(price)>=all(select sum(price)
from sales
group by seller_id)
#法3:自己建立排序变量
/*
思路就是
1.采用分组,查询出每个销售员的总销售额,并从高到低显示
2.利用变量根据总销售额排序,如若当前销售额等于前一个销售额,则序号不变,否则序号加一
3.查询出销售额序号为1的销售员
*/
select c.seller_id
from (
select a.seller_id,@num:=if(@presum=s,@num:=@num,@num:=@num+1) num,@presum:=s
from (
select seller_id,sum(price) s
from sales
group by seller_id
order by s desc) a,(select @presum:=0,@num:=0) b) c
where c.num=1