DS_CALC_Procedure/OTHER_SQL/正式库NET表里递归查某一个网络.sql

SELECT
G.会员编号,
G.会员姓名,
G.推荐编号,
G.推荐姓名,
V.会员荣衔,
V.SORT
FROM
(SELECT
P.USER_NAME AS 会员编号,
I.REAL_NAME AS 会员姓名,
O.REAL_NAME AS 推荐姓名,
P.PARENT_NAME AS 推荐编号
FROM
(WITH RECURSIVE T_REC AS -- RECURSIVE为关键字，可以不列出字段列表
(
 SELECT
  M.USER_ID,
  U.USER_NAME,
  M.INTRODUCER_ID,
  U2.USER_NAME AS PARENT_NAME,
  1 AS NODE_LEVEL 
 FROM
  AR_BS_BONUS_103_CALC_NET M 
  LEFT JOIN AR_USER U ON U.ID = M.USER_ID
  LEFT JOIN AR_USER U2 ON U2.ID = M.INTRODUCER_ID
 WHERE
  U.USER_NAME = 'E41796338' UNION ALL
 SELECT
  T1.USER_ID,
  U.USER_NAME,
  T1.INTRODUCER_ID,
  U2.USER_NAME AS PARENT_NAME,
  T2.NODE_LEVEL + 1 -- 结点层级
  FROM
  AR_BS_BONUS_103_CALC_NET T1
  JOIN T_REC T2 ON T1.INTRODUCER_ID = T2.USER_ID -- 递归生成的结果集T2的PARENT_ID与之前的源表ID关联
  LEFT JOIN AR_USER U ON U.ID = T1.USER_ID
  LEFT JOIN AR_USER U2 ON U2.ID = T1.INTRODUCER_ID
 ) SELECT
 * 
FROM
 T_REC) P
	LEFT JOIN AR_USER I ON I.USER_NAME = P.USER_NAME
	LEFT JOIN AR_USER O ON O.USER_NAME = P.PARENT_NAME
	LEFT JOIN AR_BS_BONUS_103_CALC_NET E ON E.USER_ID = P.USER_ID) G
	LEFT JOIN 
	(SELECT 
 	U.USER_NAME AS 会员编号,
 	U.REAL_NAME AS 会员姓名,
 	L2.LEVEL_NAME AS 会员荣衔,
 	L2.SORT
FROM 
AR_CALC_BONUS_BS A
LEFT JOIN AR_EMPLOY_LEVEL L2 ON A.LEVEL_ID = L2.ID
LEFT JOIN AR_USER U ON U.ID = A.USER_ID 
WHERE A.PERIOD_NUM = 159 AND A.ORI_BONUS > 0 AND L2.LEVEL_NAME != '无聘级')V ON V.`会员编号` = G.会员编号