Copy Code code as follows:
--Sales Champion
--Question: In the company, the boss came in and asked for a sales report of the top 3 sales in each region.
---
CREATE TABLE Salesdetail
(
area int is not NULL,
Saler nvarchar is not NULL,
Salerid int NOT NULL,
Sales NOT NULL
)
Insert Salesdetail
Select 1, ' John ', 15,3000
Union Select 1, ' Zhao Yi ', 16,3500
Union Select 1, ' Money two ', 17,4000
Union Select 1, ' Sun San ', 18,5000
Union select 1, ' Dick ', 19,5000
Union Select 1, ' Harry ', 11,7000
Union select 2, ' Around one ', 25,3000
Union select 2, ' Li Bai ', 22,4000
Union select 2, ' Zhang Zhen Dong ', 23,6000
Union select 2, ' Li Ning ', 24,1000
Union Select 3, ' Reese ', 35,3000
Union select 3, ' Li Yong ', 33,2000
Union Select 4, ' Likui ', 44,5000
Union select 4, ' Song Jiang ', 45,5000
Union select 4, ' Wu used ', 42,13000
Union Select 4, ' Gongsun wins ', 43,23000
Union Select 5, ' Nanxiao ', 51,5000
Union Select 5, ' Rancho ', 52,5000
Union Select 5, ' Lin Chong ', 53,5000
Union Select 5, ' Jungle ', 54,6000
Go
---The following SQL statement automatically deletes all rows of the smallest sales, and can only get data that is larger than the minimum sales
-If you have a minimum of 3 lines of sales, the largest single line, as shown in Region 5, will only get the largest line.
-area 4 can only get two lines, for the same reason.
SELECT * FROM Salesdetail as a
Where Sales >= (select min (b.sales)
From Salesdetail as B where A.area=b.area and A.sales<=b.sales
--group by sales
Having COUNT (distinct b.saler) <=3)
ORDER BY A.area,a.sales Desc,a.saler,a.salerid
Go
---use rank () to assign a sequential number to each row in each partition, and if there are duplicate values, they will be assigned the same sequence number.
Select A.area,a.saler,seq from
(
Select Area,saler,rank () over (PARTITION by the sales desc) as seq from Salesdetail
) A Where seq<=3
drop table Salesdetail
