sql 怎么查出一个表中三级关联的所有数据

[qq_29862715]

一条数据有一个code，一个father_code。数据是三级关联，第一级是code有一个father_code；第二级是第一级的father_code作为code，有一个father_code；第三级是第二级的father_code 作为code，没有father_code。怎么根据第三级的code把关联的所有数据全部查出来？

[中国风]

e.g.

SELECT 
*
FROM Table1 AS a
	INNER JOIN TABLE1 AS b ON a.code=b.father_code
	INNER JOIN Table1 AS c ON b.code=c.father_code
WHERE c.code='条件'



--查所有三级
SELECT 
*
FROM Table1 AS a
	INNER JOIN TABLE1 AS b ON a.code=b.father_code
	INNER JOIN Table1 AS c ON b.code=c.father_code
WHERE a.father_code=''--第1级没father_code

[道素]

如果不限制级别用CTE递归，如果固定三级，直接在自关联两次就行了 下面是固定界别，查询某个code及其以下子code的数据

;WITH t(code,pcode)AS
(
   select 1,0 UNION
   select 2,1 UNION
   select 3,2 UNION
   select 4,1 UNION
   select 5,0
),d(code,data)AS
(
   select 1,'a' UNION 
   select 2,'b' UNION 
   select 3,'c' UNION 
   select 4,'d' UNION 
   select 5,'5'
)
select d.* from d 
INNER JOIN t as t1 on t1.code=d.code
LEFT JOIN t as t2 on t2.code=t1.pcode
left join t as t3 on t3.code =t2.pcode
where '1' in (t1.code,t2.code,t3.code)

[Kongdom12138]

感觉用递归好一点 ;WITH classroot AS ( SELECT code,father_code,3 AS c_level --第一层为1 FROM table a WITH (NOLOCK) WHERE a.code = '数据的code' UNION ALL SELECT a.code,a.father_code,b.c_level-1 FROM table a WITH (NOLOCK) INNER JOIN classroot b ON b.father_code = a.code ) SELECT code, father_code, c_level 层级 FROM classroot