DS_CALC_Procedure/OTHER_SQL/推荐网络向上查询（递归查询版）.sql

WITH recursive t_rec AS -- recursive为关键字，可以不列出字段列表
(
	SELECT
		m.USER_ID,
		U.USER_NAME,
		m.PARENT_UID,
		U2.USER_NAME AS PARENT_NAME,
		1 AS node_level 
	FROM
		ar_user_relation_new m 
		LEFT JOIN ar_user U ON U.ID = m.USER_ID
		LEFT JOIN ar_user U2 ON U2.ID = m.PARENT_UID
	WHERE
		U.USER_NAME = 'Y99670725' UNION ALL
	SELECT
		t1.USER_ID,
		U.USER_NAME,
		t1.PARENT_UID,
		U2.USER_NAME AS PARENT_NAME,
		t2.node_level + 1 -- 结点层级
		
	FROM
		ar_user_relation_new t1
		JOIN t_rec t2 ON t2.PARENT_UID = t1.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.PARENT_UID
	) SELECT
	* 
FROM
	t_rec