求一个表中某个分类前几条的记录(这样做效率其实不高，只是做记录一下)-优快云博客

本文介绍了一个复杂的SQL查询构造过程，通过动态SQL的方式实现多层关联查询。该查询用于从产品和类别表中获取特定层级的产品信息，并将这些信息与管理员表进行联接，最终返回所需的产品名称、图片等详细信息。

< 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'