SQL Server takes the first few data from each group

First build the table statement:

 ifExists (Select* fromsysobjectswhereid = object_id ('[Test]') and OBJECTPROPERTY (ID,'isusertable') =1DROP table [test] CREATE table [test] ([ID] [int] IDENTITY (1,1) not NULL, [name] [nvarchar] ( -) NULL, [Votenum] [int] NULL, [type] [nvarchar] ( -) ALTER TABLE [test] with NOCHECK ADD CONSTRAINT [pk_test] PRIMARY KEY nonclustered ([id]) SET identity_inse RT [test] on INSERT [test] ([id], [name], [Votenum], [type]) VALUES (1,'Zhang San', the,'Scenery'INSERT [Test] ([id], [name], [Votenum], [type]) VALUES (2,'Beijing Yunmeng Mountain', About,'Scenery'INSERT [Test] ([id], [name], [Votenum], [type]) VALUES (3,'Shing', -,'Scenery'INSERT [Test] ([id], [name], [Votenum], [type]) VALUES (4,'Baiyun Dong', $,'Scenery'INSERT [Test] ([id], [name], [Votenum], [type]) VALUES (5,'Flat Magpie Temple', About,'Monuments'INSERT [Test] ([id], [name], [Votenum], [type]) VALUES (6,'Golden Wall', A,'Monuments'INSERT [Test] ([id], [name], [Votenum], [type]) VALUES (7,'Summer Resort', the,'Monuments'INSERT [Test] ([id], [name], [Votenum], [type]) VALUES (8,'West Cedar Slope', the,'Monuments'INSERT [Test] ([id], [name], [Votenum], [type]) VALUES (9,'Saihanba', -,'Grassland'INSERT [Test] ([id], [name], [Votenum], [type]) VALUES (Ten,'Prairie Tin Road', -,'Grassland'INSERT [Test] ([id], [name], [Votenum], [type]) VALUES ( One,'Beijing North Prairie', $,'Grassland'INSERT [Test] ([id], [name], [Votenum], [type]) VALUES ( A,'Merrill Lynch Valley', the,'Grassland') SET identity_insert [test] OFF

field meaning : Name of the scenic spot, the number of tickets to Votenum Scenic area, type of scenic spot

implementation function : query Each classification of the two most votes of the scenic area, by the number of votes from high to low ranking

Implementation 1: Row_number () with partition by grouping, take the first two bits, easy to understand

Select  from (   Select from test) b    where b.vn<=2 ORDER BY [ Type], Votenum desc

Implementation 2: self-connect

Select  from  where (selectfromwhere A.type=b.type and A.votenum<b.votenum) <= 1 ORDER BY [Type],votenum desc

One of the data in the outer table A finds the same type in the inner table B, and the number of votes in Table B is greater than the number of votes for that record in table A (count),

If it is less than 1, the number of votes for this data in table A is highest or second high, then the data in table A is returned

Implementation 3:

Select  from  wherein (select2fromwhere a.type=b.type Order by b.votenum Desc)   ORDER BY [Type],votenum desc

SQL Server takes the first few data from each group