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