Sql server循环查询示例

    WITH    cc(mainid, materialid, pmaterialid, level)
              AS (SELECT    mainid, materialid, pmaterialid, 0 AS levelFROM      dbo.eval_ebom
                  WHERE     mainid = @mainid AND materialid = @materialid
                  UNION--含本级其他兄弟节点，可以根据需要取舍
                  SELECT    mainid, materialid, pmaterialid, 0 AS levelFROM      dbo.eval_ebom
                  WHERE     mainid = @mainid AND pmaterialid = (SELECT pmaterialid FROM eval_ebom WHERE mainid= @mainid) 
　　　　　　　　　　 UNION ALL
                  SELECT    a.mainid, a.materialid, a.pmaterialid, cc.level + 1FROM      dbo.eval_ebom a
                            INNER JOIN cc ON a.mainid = cc.mainid AND a.materialid = cc.pmaterialid 
　　　　　　　　)
SELECT  * FROM    cc