1082. Sales Analysis I 难度：简单

1、题目描述

Write an SQL query that reports the best seller by total sales price, If there is a tie, report them all.
The query result format is in the following example:

Product table:

product_id	product_name	unit_price
1	S8	1000
2	G4	800
3	iPhone	1400

Sales table:

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 table:

seller_id
1
3

Both sellers with id 1 and 3 sold products with the most total price of 2800.

来源：力扣（LeetCode）

2、解题思路

其实和上一题都是差不多的，这次换一种解法
1# 首先，对每个销售员进行出售价格求和，然后倒序

select seller_id ,sum(price) as p
from Sales
group by seller_id
order by price desc

2# 然后，增加一列排名列 参考 分数排名
select seller_id ,p,@j:=@j+(@i<>(@i:=p)) as rank
3# 最后，就是找出rank=1的记录

3、提交记录

select seller_id
from(
select  seller_id ,p,@j:=@j+(@i<>(@i:=p)) as rank
from(
select seller_id ,sum(price) as p
from Sales
group by seller_id
order by price desc)b,(select @i:=0,@j:=0)a)c
where rank=1
order by seller_id