一条sql获取每个类别最新的一条记录 - 后端技术

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最⼤⼀条记录。这样就很好的实现了批量查询最近记录，并且仅仅需要遍历⼀次表，即使在数据量巨⼤的情况下也可以在很短的时间查出结果
如果是主键⼦⾃增,则可以直接求主键的最⼤值