面试题,找出每个产品的最新五个产品,还有其它方法吗 —— 游标加表变量
表t_c 和t_p如上图
select * from t_c
select * from t_p
查询方法一:
select * from (select row_number() over(partition by cid order by cdate desc) as rowid,* from t_p) T where T.rowid<=5
使用sql2005的特性 row_number() over()
方法二:
declare @temp table(id int,cid int,cdate datetime);
declare @id int;
declare cur cursor for
select cid from t_c
open cur
fetch next from cur Into @id
while @@fetch_status = 0
begin
insert into @temp
select top 5 * from t_p where cid = @id order by cdate desc
fetch next from cur into @id
end
close cur
deallocate cur
select * from @temp
利用游标和表变量
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步