WITH cte AS ( SELECT a.FNUMBER,a.FMATERIALID AS MainId,b.FMATERIALID AS ChileID,CAST(b.FMATERIALID AS VARCHAR(max)) AS lev FROM t_eng_bom a JOIN dbo.T ...
WITH cte AS (
SELECT a.FNUMBER,a.FMATERIALID AS MainId,b.FMATERIALID AS ChileID,CAST(b.FMATERIALID AS VARCHAR(max)) AS lev FROM t_eng_bom a
JOIN dbo.T_ENG_BOMCHILD b ON a.fid =b.FID
WHERE a.fid = '100150'
UNION ALL
SELECT d.FNUMBER,d.MainId,d.ChileID,CAST(lev AS VARCHAR(max))+ CAST(d.ChileID AS VARCHAR(max)) AS lev FROM cte c
JOIN ( SELECT aa.FNUMBER,aa.FMATERIALID AS MainId,bbb.FMATERIALID AS ChileID FROM t_eng_bom aa
JOIN dbo.T_ENG_BOMCHILD bbb ON aa.fid =bbb.FID )d ON c.ChileID = d.MainId
)
SELECT * FROM cte ORDER BY lev
WITH cte as(
select a,b,c from tablea
union all
select a,b,c from cte a
join tablea b on a.a = b.b
)
select * from cte
語法：
[WITH[,n]]
::=
expression_name[(column_name[,n])]
AS(
CTE_query_definition1 -- 定位點成員（也就是初始值或第一個結果集）
unionall
CTE_query_definition2 -- 遞歸成員
)
最後傳送門：http://blog.csdn.net/bin_520_yan/article/details/5998349#reply
