sql实现每个分类取最新几条语句

作者：袖梨 2022-06-29

在sql使用中，我们可能经常会碰到这样的需求，即每类信息取前几条显示出来。 实现的方法有很多种，但考虑到效率问题。今天我们使用sql server2005里row_number()这个函数。

ROW_NUMBER ( ) 函数的语法如下： ROW_NUMBER ( ) OVER ( [ ] )
OVER 子句中的 PARTITION BY 将结果集分为多个分区。

OVER 子句中的 ORDER BY 将对 ROW_NUMBER 进行排序。

假如要从article表中得到每个分类的10条数据，并且倒序排序，那么最终的sql语句如下：

 代码如下 复制代码

select id,title,class,addtime from(select id,title,class,addtime,row_number()
over(partition by class order by id desc) as rowindex
from article where status=1) a where rowindex<11稍微解释一下：

select id,title,class,addtime,row_number()
over(partition by class order by id desc) as rowindex
from article where status=1


首先，从article表里按类型(class)分类，并对每类进行了排序，序号为(rowindex)。最后，从每类取出前10条。即实现了我们的目的。 经过测试，此种写法效率较高，推荐大家使用


现在我们来看一个例子

 代码如下 复制代码
CREATE TABLE table1(    [ID] [bigint] IDENTITY(1,1) NOT NULL,    [Name] [nvarchar](128) NOT NULL,    [class] int not null,    [date] datetime not null)

class 表示分类编号。 分类数不固定， 至少有上千种分类
date 表示该条记录被更新的时间
我们现在想获得每个分类最新被更新的5条记录。

 

解决方案

 代码如下 复制代码

select id,name,class,date from(select id,name,class,date ,row_number() over(partition by class order by date desc)as rowindex from table1) awhere rowindex <= 5

 create table #temp

(
  company varchar(50),
  product varchar(50),
  inputDate datetime
)


insert into #temp(company,product,inputDate) values('杭州大明有限公司','汽车1','2010-8-1')
insert into #temp(company,product,inputDate) values('杭州大明有限公司','汽车2','2010-8-1')
insert into #temp(company,product,inputDate) values('杭州大明有限公司','汽车3','2010-8-1')
insert into #temp(company,product,inputDate) values('杭州大明有限公司','汽车4','2010-8-1')
insert into #temp(company,product,inputDate) values('杭州大明有限公司','汽车5','2010-7-1')


insert into #temp(company,product,inputDate) values('北京小科有限公司','汽车1','2010-8-1')
insert into #temp(company,product,inputDate) values('北京小科有限公司','汽车2','2010-8-1')
insert into #temp(company,product,inputDate) values('北京小科有限公司','汽车3','2010-8-1')
insert into #temp(company,product,inputDate) values('北京小科有限公司','汽车4','2010-8-1')


insert into #temp(company,product,inputDate) values('上海有得有限公司','汽车1','2010-8-1')
insert into #temp(company,product,inputDate) values('上海有得有限公司','汽车2','2010-8-1')
insert into #temp(company,product,inputDate) values('上海有得有限公司','汽车3','2010-8-1')
insert into #temp(company,product,inputDate) values('上海有得有限公司','汽车4','2010-8-1')


insert into #temp(company,product,inputDate) values('天津旺旺有限公司','汽车4','2010-8-1')
insert into #temp(company,product,inputDate) values('天津旺旺有限公司','汽车5','2010-8-1')


select * from #temp


create proc getdata


@num int
as


begin


select  top 4 *  from 


(
select ( select count(*) from #temp where company=a.company and product<=a.product) as 序号,a.company,a.product,a.inputDate
from #temp a
) b
where 序号>=@num


order by  序号,inputDate desc


end


go
getdata 2

 


/*


结果


1    杭州大明有限公司    汽车1    2010-08-01 00:00:00.000
1    北京小科有限公司    汽车1    2010-08-01 00:00:00.000
1    上海有得有限公司    汽车1    2010-08-01 00:00:00.000
1    天津旺旺有限公司    汽车4    2010-08-01 00:00:00.000

 

2    天津旺旺有限公司    汽车5    2010-08-01 00:00:00.000
2    上海有得有限公司    汽车2    2010-08-01 00:00:00.000
2    北京小科有限公司    汽车2    2010-08-01 00:00:00.000
2    杭州大明有限公司    汽车2    2010-08-01 00:00:00.000

 

3    杭州大明有限公司    汽车3    2010-08-01 00:00:00.000
3    北京小科有限公司    汽车3    2010-08-01 00:00:00.000
3    上海有得有限公司    汽车3    2010-08-01 00:00:00.000
4    北京小科有限公司    汽车4    2010-08-01 00:00:00.000

 

 

4    北京小科有限公司    汽车4    2010-08-01 00:00:00.000
4    上海有得有限公司    汽车4    2010-08-01 00:00:00.000
4    杭州大明有限公司    汽车4    2010-08-01 00:00:00.000
5    杭州大明有限公司    汽车5    2010-07-01 00:00:00.000

 


*/

 


--sql2005
create proc getdata2005
@num int
as
begin
select  top 4 *  from
(
select row_number() over (partition by company order by product ) as 序号,a.company,a.product,a.inputDate
from #temp a
) b
where 序号>=@num
order by  序号,inputDate desc
end

 

getdata2005 4


select *   from  #temp


select ( select count(*) from #temp where  company+ product<=a.company+a.product) as 序号,a.company,a.product,a.inputDate
,a.company+a.product as 唯一标志一行
from #temp a
order by company,product

相关文章

精彩推荐