sqlServer 取每组的前几条数据

exists (select * from sysobjects where id = OBJECT_ID(‘[test]‘) and OBJECTPROPERTY(id, ‘IsUserTable‘) = 1) DROP TABLE [test] CREATE TABLE [test] ( [id] [int] IDENTITY (1, 1) NOT NULL , [name] [nvarchar] (50) NULL , [votenum] [int] NULL , [type] [nvarchar] (50) 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 ( 1 , ‘张三‘ , 88 , ‘风景‘ ) INSERT [test] ( [id] , [name] , [votenum] , [type] ) VALUES ( 2 , ‘云蒙山‘ , 99 , ‘风景‘ ) INSERT [test] ( [id] , [name] , [votenum] , [type] ) VALUES ( 3 , ‘抱犊寨‘ , 59 , ‘风景‘ ) INSERT [test] ( [id] , [name] , [votenum] , [type] ) VALUES ( 4 , ‘白云洞‘ , 46 , ‘风景‘ ) INSERT [test] ( [id] , [name] , [votenum] , [type] ) VALUES ( 5 , ‘扁鹊庙‘ , 56 , ‘古迹‘ ) INSERT [test] ( [id] , [name] , [votenum] , [type] ) VALUES ( 6 , ‘金长城‘ , 22 , ‘古迹‘ ) INSERT [test] ( [id] , [name] , [votenum] , [type] ) VALUES ( 7 , ‘避暑山庄‘ , 69 , ‘古迹‘ ) INSERT [test] ( [id] , [name] , [votenum] , [type] ) VALUES ( 8 , ‘西柏坡‘ , 87 , ‘古迹‘ ) INSERT [test] ( [id] , [name] , [votenum] , [type] ) VALUES ( 9 , ‘塞罕坝‘ , 48 , ‘草原‘ ) INSERT [test] ( [id] , [name] , [votenum] , [type] ) VALUES ( 10 , ‘草原天路‘ , 44 , ‘草原‘ ) INSERT [test] ( [id] , [name] , [votenum] , [type] ) VALUES ( 11 , ‘京北草原‘ , 36 , ‘草原‘ ) INSERT [test] ( [id] , [name] , [votenum] , [type] ) VALUES ( 12 , ‘美林谷‘ , 77 , ‘草原‘ ) 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<=2 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) <=1
 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 2 b.id from test b where a.type=b.type order by b.votenum desc)
   order by [type],votenum desc

 

sqlServer 取每组的前几条数据

标签：