sqlserver 存储过程 递归查询分组+hierarchyid重建会员关系

16) = 0 --会员卡号 AS BEGIN declare @p int; --查询唯一性结果 declare @sql nvarchar(1000); --拼接查询sql字串 set @sql=‘SELECT @p=n_teamlevel FROM t_user_basic WHERE s_code=‘ +@s_code; exec sp_executesql @sql,N‘@p AS int OUTPUT‘,@p OUTPUT; with cte( NodeID,n_user,s_code,s_introducer,n_teamlevel ) as( SELECT NodeID=hierarchyID::Parse(‘/1/‘) ,n_user ,s_code ,s_introducer ,n_teamlevel FROM t_user_basic WHERE s_code=@s_code union all SELECT NodeID=cast(b.NodeID.ToString() + cast(ROW_NUMBER() over (order by a.n_user) as varchar(10)) + ‘/‘ as hierarchyid) ,a.n_user ,a.s_code ,a.s_introducer ,a.n_teamlevel FROM t_user_basic a inner join cte b on (a.s_introducer=b.s_code) WHERE a.s_introducer<>-1 and a.n_user>0 and a.n_teamlevel < @p ) --SELECT * FROM cte WHERE n_teamlevel < @p and s_code <> @s_code and n_teamlevel <> null; SELECT cast(NodeID as hierarchyid).ToString(),n_user,s_code,s_introducer,n_teamlevel FROM cte END ---execute [dbo].[GetGroupInfo] ‘864160951‘

 

sqlserver 存储过程 递归查询分组+hierarchyid重建会员关系

标签：rar   output   from   group   tostring   user   sql   var   join