SQL语句查询每个分组的前N条记录的实现方法:
1、生成测试数据: #T
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
if object_id( 'tempdb.dbo.#T' ) is not null drop table #T; create table #T (ID varchar (3), GID int , Author varchar (29), Title varchar (39), Date datetime); insert into #T select '001' , 1, '邹建' , '深入浅出SQLServer2005开发管理与应用实例' , '2008-05-10' union all select '002' , 1, '胡百敬' , 'SQLServer2005性能调校' , '2008-03-22' union all select '003' , 1, '格罗夫Groff.J.R.' , 'SQL完全手册' , '2009-07-01' union all select '004' , 1, 'KalenDelaney' , 'SQLServer2005技术内幕存储引擎' , '2008-08-01' union all select '005' , 2, 'Alex.Kriegel.Boris.M.Trukhnov' , 'SQL宝典' , '2007-10-05' union all select '006' , 2, '飞思科技产品研发中心' , 'SQLServer2000高级管理与开发' , '2007-09-10' union all select '007' , 2, '胡百敬' , 'SQLServer2005数据库开发详解' , '2008-06-15' union all select '008' , 3, '陈浩奎' , 'SQLServer2000存储过程与XML编程' , '2005-09-01' union all select '009' , 3, '赵松涛' , 'SQLServer2005系统管理实录' , '2008-10-01' union all select '010' , 3, '黄占涛' , 'SQL技术手册' , '2006-01-01' union all select '010' , 4, '黄蛋蛋' , 'SQL技术手册蛋蛋' , '2006-01-01' ; |
2、表记录查询如下:
1
|
select * from #T; |
结果:
3、按GID分组,查每个分组中Date最新的前2条记录
(1)用子查询
1
2
3
4
5
6
7
8
9
|
--1.字段ID唯一时: select * from #T as T where ID in ( select top 2 ID from #T where GID=T.GID order by Date desc ); --2.如果ID不唯一时: select * from #T as T where 2>( select count (*) from #T where GID=T.GID and Date >T. Date ); |
(2)使用SQL Server 2005 使用新方法ROW_NUMBER()进行排位分组
1
2
3
4
|
select ID, GID, Author, Title, Date from ( select rid=row_number() over (partition by GID order by Date desc ), * from #T) as T where rid<=2; |
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持服务器之家。
原文链接:https://www.cnblogs.com/springsnow/p/13491347.html