sqlserver 存储过程 递归查询分组+hierarchyid重建会员关系
标签：rar output from group tostring user sql var join
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‘