一条sql获取每个类别最新的一条记录

2022-11-16 09:39:00
pjd
原创 185

1.初始化数据


create table 
Products(id int identity(1,1),name nvarchar(100),categroy int,addtime datetime) 
insert into Products (name ,categroy,addtime) 
values ('手机',1,'2016-10-01 00:00:00.000'), ('笔记本',1,'2016-10-02 00:00:00.000'), ('照相机',1,'2016-10-03 00:00:00.000'), ('上衣',2,'2016-10-01 00:00:00.000'), ('短袖',2,'2016-10-02 00:00:00.000'), ('裤子',2,'2016-10-03 00:00:00.000')


2.示例:

--id是主键

select * From Products A where id in(select top1 id from Products where categroy=A.categroy order by addtime desc)

--id 不是主键

select * From Products A where 1 > (select count(1) from Products where categroy=A.categroy and addtime >a.addtime )

--row_number


select * From (select row_number() over(partition by categroy order by addtime desc) rownum, * from Products ) A where rownum < 2


--cross apply


select distinct B.* from Products A cross apply (select top1 * From Products where categroy = A.categroy order by addtime desc) B


3.其它方案


下策——查询出结果后将时间排序后取第⼀条

select * from a where create_time = 2017-03-2919:30:36 order by create_time desc limit 1
这样做虽然可以取出当前时间最近的⼀条记录,但是⼀次查询需要将表遍历⼀遍,对于百万以上数据查询将⽐较费时;limit是先取出全部结果,然后取第⼀条,相当于查询中占⽤了不必要的时间和空间;还有如果需要批量取出最近⼀条记录,⽐⽅说:“⼀个订单表,有⽤户,订单时间,⾦额,需要⼀次性查询所有⽤户的最近的⼀条订单记录”,那么每个⽤户⼀次查询就要做⼀次整表的遍历,数据⼤的情况下,时间将会以指数形式增长,不能投⼊实际使⽤。中策——查询排序后groupby
select * from (select * from a where create_time = 2017-03-2919:30:36 order by create_time desc ) group by user_id
后来发现使⽤groupby可以根据groupby的参数列分组,但返回的结果只有⼀条,仔细观察发现groupby是将分组后的第⼀条记录返回。时间在查询后默认是顺序排列,因此需要先将时间倒序排列,⽅可取出距离当前最近⼀条。
这样查询实际上还是进⾏了两次查询,虽然时间上相⽐第⼀个⽅法有了质的飞跃,但是还可以进⼀步优化。上策——将max()⽅法和groupby结合使⽤
select *,max(create_time) from a where create_time = 2017-03-2919:30:36 group by user_id
这句可以理解为将结果集根据user_id分组,每组取time最⼤⼀条记录。这样就很好的实现了批量查询最近记录,并且仅仅需要遍历⼀次表,即使在数据量巨⼤的情况下也可以在很短的时间查出结果
如果是主键⼦⾃增,则可以直接求主键的最⼤值



发表评论
评论通过审核后显示。