MS SQL Server递归查询数据

 

表结构

 

 

CREATE   TABLE  District (
    DisCode             VARCHAR ( 10 ),
    DisName             VARCHAR ( 10 ),
    DisFatherCode     VARCHAR ( 10 ),
    HasChild         BIT
    )
    

 

 

代码

; WITH  AllDiscodes  AS  (
             -- 递归查询出@discode的父级地域，用于筛选最匹配的打折信息
             SELECT  discode,
                   d.DisFatherCode
             FROM    District d
             WHERE   d.DisCode  =   ' 652324522218 '
             UNION   ALL
             SELECT  d.discode,
                   d.DisFatherCode
             FROM    District d
                    INNER   JOIN  AllDiscodes ad
                         ON   d.discode  =  ad.DisFatherCode
        ) SELECT   *   FROM  alldiscodes
        
        

 

 

复杂一点，树形结构表和其他表有关联
 

 

代码

-- 复杂一点，树形结构表和其他表有关联
; WITH  targets  AS  (
         SELECT   DISTINCT  t.TargetCode,
               t.TargetFatherCode,
               t.TargetName,
               t.Haschild
         FROM     [ Target ]  t
                INNER   JOIN  CropTarget ct
                     ON   t.TargetCode  =  ct.TargetCode
                     AND  ct.CropClassCode  =   ' zw00010001 '
         UNION   ALL
         SELECT  t.TargetCode,
               t.TargetFatherCode,
               t.TargetName,
               t.Haschild
         FROM     [ Target ]  t
                INNER   JOIN  targets ts
                     ON   ts.TargetFatherCode  =  t.targetcode
    )
     SELECT   DISTINCT  ts. * ,t.TargetName  AS  TargetFatherName
     FROM    targets ts  left   JOIN   [ Target ]  t  ON  ts.targetfathercode = t.TargetCode