sqlServer 取每组的前几条数据

首先的建表语句:

 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 景区类型

实现功能:查询各分类中投票最多的两个景区,按投票数从高到低排序

sqlServer 取每组的前几条数据

实现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
上一篇:InnoDB和MyISAM存储引擎的区别


下一篇:从setTimeout谈js运行机制