MS SQL Server递归查询数据
2010-11-26 13:49 Virus-BeautyCode 阅读(1435) 评论(1) 编辑 收藏 举报
表结构
CREATE TABLE District (
DisCode VARCHAR(10),
DisName VARCHAR(10),
DisFatherCode VARCHAR(10),
HasChild BIT
)
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
--递归查询出@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
--复杂一点,树形结构表和其他表有关联
;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