求一个表中某个分类前几条的记录(这样做效率其实不高,只是做记录一下)

  1. 云栖社区>
  2. 博客>
  3. 正文

求一个表中某个分类前几条的记录(这样做效率其实不高,只是做记录一下)

技术小美 2017-11-12 22:30:00 浏览531
展开阅读全文
< DOCTYPE html PUBLIC -WCDTD XHTML StrictEN httpwwwworgTRxhtmlDTDxhtml-strictdtd>

declare @sql as nvarchar(max),@sql1 as nvarchar(max),@sql2 as nvarchar(max),@i as int,@j as int,@l as int,@tmp as nvarchar(max),@stri as nvarchar(5)

set @sql1='';
set @i=0
set @l=3
set @sql2=''
while(@i<@l)
begin
 set @stri=cast(@i as nvarchar(5))
 set @sql='declare @max'+@stri+' as table(id bigint,parentid int);
 insert into @max'+@stri+'(id,parentid)
 select max(id),parentid
 from Products inner join Classes on Classes.classid=Products.classid
 where parentid>0 '
 set @j=0
 set @tmp=''
 while(@j<@i)
 begin
  set @tmp=@tmp+' and id not in(select id from @max'+cast(@j as nvarchar(5))+')'
  set @j=@j+1
 end
 set @sql=@sql+@tmp+' group by ParentID;'
 set @sql1=@sql1+@sql;
 if(@i>0) set @sql2=@sql2+' union '
 set @sql2=@sql2+'select * from @max'+@stri+' '
 set @i=@i+1
end
set @sql=@sql1+
'select ProductName,ProductDpic,1 as indexID,Products.ID,UserName,RealName,Products.Addtime,ParentID from
Products inner join Admin_User on Admin_User.id=Products.userid inner join ('
+@sql2+') as t on t.id=products.id'

exec sp_executesql  @sql

posted @ 2011-04-03 13:54 netcorner 阅读(43) 评论(0编辑 收藏

网友评论

登录后评论
0/500
评论
技术小美
+ 关注