SQL 递归算法 行转列
写SQL 根据要求需要取分类的头四级显示出来,分类无极数递归
例如: 手机->三星->盖世兔->1
->HTC->G系统->G14->G14简版
->HTC->G系统->G14->G14起航版->..... 无数级
电脑->三星->R系列->1
->惠普->G系统->G14->G14简版
->IBM->G系统->G1->G1起航版->..... R40
要求根据最底层的求出前四级显示
R40已经 返回 电脑 IBM G系统 G1
ALTER FUNCTION [dbo].[fn_RefCategorys] (@PCID int) RETURNS @RefValueCategorys TABLE ( one int, two int, three int, four int ) AS begin ;with hgo as ( select PCID,ParentID,0 as rank,0 as id from [ProductCategorys] where PCID=@PCID union all select h.PCID,h.ParentID,h1.rank+1,0 as id from [ProductCategorys] h join hgo h1 on h.PCID=h1.ParentID ) insert into @RefValueCategorys select IsNULL(max(case t.rank when b.aa-1 then t.PCID end),0) as one, IsNULL(max(case t.rank when b.aa-2 then t.PCID end),0) as two, IsNULL(max(case t.rank when b.aa-3 then t.PCID end),0) as three, IsNULL(max(case t.rank when b.aa-4 then t.PCID end),0) as fist from hgo t ,(select COUNT(1) as aa from hgo) b group by t.id; return end