表结构
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
)
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
;
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
复杂一点,树形结构表和其他表有关联
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
-- 复杂一点,树形结构表和其他表有关联
; 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