你的位置:首页 > 数据库

[数据库]Sql Server CTE递归


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