with语法，需要递归的面试题目

http://www.dusystem.com/bbs/ShowPost.asp?ThreadID=5631

 with CET as 
 ( 
select C1,C2,1 as l1,2 as [l2] from a where c1='A'  
    union all   
select a.*,l1+1,l2+1 from CET inner join a on CET.c2=a.c1
) 
select * from
(
select c1 as jd,l1 as [level] from cet
union
select c2,l2 as [level] from cet
)t
order by [level]

http://www.cnblogs.com/nokiaguy/archive/2009/01/31/1381562.html

这里有比较详细的讲解。