ms sql server递归查询

jlthlx

　　表结构
CREATE TABLE District (　　DisCode
VARCHAR(10),　　DisName
VARCHAR(10),　　DisFatherCode
VARCHAR(10),　　HasChild
BIT　　)
复制代码
http://images.cnblogs.com/OutliningIndicators/ContractedBlock.gifhttp://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif代码;WITH AllDiscodes AS (--递归查询出@discode的父级地域，用于筛选最匹配的打折信息SELECT discode,　　d.DisFatherCode
FROM District dWHERE d.DisCode = '652324522218'UNION ALLSELECT d.discode,　　d.DisFatherCode
FROM District dINNER JOIN AllDiscodes adON d.discode = ad.DisFatherCode　　)
SELECT * FROM alldiscodes复制代码　　复杂一点，树形结构表和其他表有关联

http://images.cnblogs.com/OutliningIndicators/ContractedBlock.gifhttp://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif代码--复杂一点，树形结构表和其他表有关联;WITH targets AS (SELECT DISTINCT t.TargetCode,　　t.TargetFatherCode,
　　t.TargetName,
　　t.Haschild
FROM [Target] tINNER JOIN CropTarget ctON t.TargetCode = ct.TargetCodeAND ct.CropClassCode = 'zw00010001'UNION ALLSELECT t.TargetCode,　　t.TargetFatherCode,
　　t.TargetName,
　　t.Haschild
FROM [Target] tINNER JOIN targets tsON ts.TargetFatherCode = t.targetcode　　)
SELECT DISTINCT ts.*,t.TargetName AS TargetFatherNameFROM targets ts left JOIN [Target] t ON ts.targetfathercode=t.TargetCode 复制代码