Copy codeThe Code is as follows: -- sales champion
-- Problem: in the company, the boss came in and asked for a report of the sales and sales staff of the top 3 sales in each region.
---
Create table salesdetail
(
Area int not null,
Saler nvarchar (20) not null,
SalerId int not null,
Sales money not null
)
Insert salesdetail
Select 1, 'zhang san ',
Union select 1, 'zhao yi', 16, 3500
Union select 1, 'money 2 ',
Union select 1, 'sunsan ',
Union select 1, 'lily ',
Union select 1, 'wang wu', 11,7000
Union select 2, 'peripheral 1 ',
Union select 2, 'Li Bai ',
Union select 2, 'zhang Zhen Dong ',
Union select 2, 'Li Ning ', 24
Union select 3, 'lies', 35
Union select 3, 'Li Yong ',
Union select 4, 'Li yun', 44,5000
Union select 4, 'songjiang ',
Union select 4, 'wu yong ',
Union select 4, 'Sun sheng', 43,23000
Union select 5, 'employee Kid ',
Union select 5, 'divide small 5 ',
Union select 5, 'lin chong ',
Union select 5, 'limit', 54,6000
Go
--- The following SQL statement will automatically delete all rows with the smallest sales volume and only obtain data larger than the minimum sales volume.
-- If your minimum sales volume has three rows and only one row is the largest, as shown in Area 5, only the row with the largest will be obtained.
-- Region 4 can only get two rows, for the same reason.
Select * from salesdetail as
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 sequence number to each row in each partition. If there are duplicate values, they will all be assigned the same sequence number.
Select a. area, a. saler, seq from
(
Select area, saler, RANK () over (PARTITION by area order by sales desc) as seq from salesdetail
) A where seq <= 3
Drop table salesdetail
