首先的建表语句：
if exists (select * from sysobjects where id = OBJECT_ID('[test]') and OBJECTPROPERTY(id, 'IsUserTable') = ) DROP TABLE [test]
CREATE TABLE [test] ( [id] [int] IDENTITY (, ) NOT NULL , [name] [nvarchar] () NULL , [votenum] [int] NULL , [type] [nvarchar] () NULL )
ALTER TABLE [test] WITH NOCHECK ADD CONSTRAINT [PK_test] PRIMARY KEY NONCLUSTERED ( [id] )
SET IDENTITY_INSERT [test] ON
INSERT [test] ( [id] , [name] , [votenum] , [type] ) VALUES ( , '嶂石岩' , , '风景' )
INSERT [test] ( [id] , [name] , [votenum] , [type] ) VALUES ( , '云梦山' , , '风景' )
INSERT [test] ( [id] , [name] , [votenum] , [type] ) VALUES ( , '抱犊寨' , , '风景' )
INSERT [test] ( [id] , [name] , [votenum] , [type] ) VALUES ( , '崆山白云洞' , , '风景' )
INSERT [test] ( [id] , [name] , [votenum] , [type] ) VALUES ( , '扁鹊庙' , , '古迹' )
INSERT [test] ( [id] , [name] , [votenum] , [type] ) VALUES ( , '金长城' , , '古迹' )
INSERT [test] ( [id] , [name] , [votenum] , [type] ) VALUES ( , '避暑山庄' , , '古迹' )
INSERT [test] ( [id] , [name] , [votenum] , [type] ) VALUES ( , '西柏坡' , , '古迹' )
INSERT [test] ( [id] , [name] , [votenum] , [type] ) VALUES ( , '塞罕坝' , , '草原' )
INSERT [test] ( [id] , [name] , [votenum] , [type] ) VALUES ( , '草原天路' , , '草原' )
INSERT [test] ( [id] , [name] , [votenum] , [type] ) VALUES ( , '京北草原' , , '草原' )
INSERT [test] ( [id] , [name] , [votenum] , [type] ) VALUES ( , '美林谷' , , '草原' )
SET IDENTITY_INSERT [test] OFF
字段含义：name 风景区名称，votenum 景区得票数量 ，type 景区类型
实现功能：查询各分类中投票最多的两个景区，按投票数从高到低排序
实现1： ROW_NUMBER() 配合partition by 按照分组进行排序，取前两位，易懂
select name,votenum,[type] from (
select *,ROW_NUMBER() over(partition by [type] order by votenum desc) vn from test) b
where b.vn<= order by [type], votenum desc
实现2：自连接
select * from test a
where (select COUNT(*) from test b where a.type=b.type and a.votenum<b.votenum) <=
order by [type],votenum desc
外层表a中的一条数据 在内层表b中查找的相同类型type，且b表中投票数量大于a表中该条记录的投票数量的记录数（count）,
如果小于1，说明a表中的这条数据 的投票数是最高或者是第二高的，则返回a表中的这条数据
实现3：
select * from test a
where a.id in(select top b.id from test b where a.type=b.type order by b.votenum desc)
order by [type],votenum desc