SQL Server 获取每组中的前N个

方法一:

--建立测试环境 
create table ta3(id varchar(4),repairvalue numeric(4,2),pieceid varchar(2),facid varchar(8)) 
insert into ta3 
select '01',4.6,'1','aaa' 
union all select '02',2,'1','aaa' 
union all select '03',0,'1','aaa' 
union all select '04',3,'1','aaa' 
union all select '05',5,'1','aaa' 
union all select '06',4.6,'2','aaa' 
union all select '07',7.8,'2','aaa' 
union all select '08',0,'2','aaa' 
union all select '09',5,'2','aaa' 
union all select '10',3,'2','aaa' 
--测试 
Select id,repairvalue,pieceid from ta3 A 
Where Not Exists(Select 1 from ta3 Where pieceid=A.pieceid And repairvalue>A.repairvalue Having Count(1)>2) --这里N取3
Order By pieceid,repairvalue Desc 
--删除测试环境 
Drop Table ta3 
--结果 
/* 
id repairvalue pieceid 
05 5.00 1 
01 4.60 1 
04 3.00 1 
07 7.80 2 
09 5.00 2 
06 4.60 2 
*/ 

方法二:





if object_id('tb') is not null
   drop table tb
go


create table tb
([id] int, [title] numeric(12,1), [typeid] int, [datetime] int)


insert into tb
select 1,1.1,1,1
union all select 2,1.2,1,2
union all select 3,1.3,1,3
union all select 4,2.1,2,4
union all select 5,2.2,2,5
union all select 6,2.3,2,6
union all select 7,3.1,3,7
union all select 8,3.2,3,8
union all select 9,3.3,3,9


select *
from
(
select *,
       
       --先按typeid分组,在一组中按照datetime降序排列,来编号
       ROW_NUMBER() over(partition by typeid 
                             order by datetime desc)  as rownum
from tb
)t
where rownum<=2  --取行号为1和2的,也就是时间最大的2条数据


/*
id<span style="white-space:pre">	</span>title<span style="white-space:pre">	</span>typeid<span style="white-space:pre">	</span>datetime<span style="white-space:pre">	</span>rownum
3<span style="white-space:pre">	</span>1.3<span style="white-space:pre">	</span>1<span style="white-space:pre">	</span>3<span style="white-space:pre">	</span>1
2<span style="white-space:pre">	</span>1.2<span style="white-space:pre">	</span>1<span style="white-space:pre">	</span>2<span style="white-space:pre">	</span>2
6<span style="white-space:pre">	</span>2.3<span style="white-space:pre">	</span>2<span style="white-space:pre">	</span>6<span style="white-space:pre">	</span>1
5<span style="white-space:pre">	</span>2.2<span style="white-space:pre">	</span>2<span style="white-space:pre">	</span>5<span style="white-space:pre">	</span>2
9<span style="white-space:pre">	</span>3.3<span style="white-space:pre">	</span>3<span style="white-space:pre">	</span>9<span style="white-space:pre">	</span>1
8<span style="white-space:pre">	</span>3.2<span style="white-space:pre">	</span>3<span style="white-space:pre">	</span>8<span style="white-space:pre">	</span>2
*/


阅读更多
文章标签: sql server
个人分类: sql
上一篇DIV自适应屏幕分辨率,垂直水平居中
下一篇JS正则表达式验证手机号、电话和邮箱
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭