SELECT a.一级分类编码,
a.一级分类名称,
a.一级分类层级,
b.二级分类编码,
b.二级分类名称,
b.二级分类层级,
c.三级分类编码,
c.三级分类名称,
c.三级分类层级,
d.四级分类编码,
d.四级分类名称,
d.四级分类层级,
e.五级分类编码,
e.五级分类名称,
e.五级分类层级
FROM
(
SELECT cInvCCode AS 一级分类编码,
cInvCName AS 一级分类名称,
iInvCGrade AS 一级分类层级
FROM InventoryClass
WHERE LEN(cInvCCode) = 1
) a
LEFT JOIN
(
SELECT cInvCCode AS 二级分类编码,
cInvCName AS 二级分类名称,
iInvCGrade AS 二级分类层级
FROM InventoryClass
WHERE LEN(cInvCCode) = 2
) b
ON a.一级分类编码 = LEFT(b.二级分类编码, 1)
LEFT JOIN
(
SELECT cInvCCode AS 三级分类编码,
cInvCName AS 三级分类名称,
iInvCGrade AS 三级分类层级
FROM InventoryClass
WHERE LEN(cInvCCode) = 4
) c
ON LEFT(b.二级分类编码, 2) = LEFT(c.三级分类编码, 2)
LEFT JOIN
(
SELECT cInvCCode AS 四级分类编码,
cInvCName AS 四级分类名称,
iInvCGrade AS 四级分类层级
FROM InventoryClass
WHERE LEN(cInvCCode) = 6
) d
ON LEFT(c.三级分类编码, 4) = LEFT(d.四级分类编码,4)
LEFT JOIN
(
SELECT cInvCCode AS 五级分类编码,
cInvCName AS 五级分类名称,
iInvCGrade AS 五级分类层级
FROM InventoryClass
WHERE LEN(cInvCCode) = 8
) e
ON LEFT(d.四级分类编码, 6) = LEFT(e.五级分类编码, 6)
ORDER BY a.一级分类编码,
a.一级分类名称,
a.一级分类层级,
b.二级分类编码,
b.二级分类名称,
b.二级分类层级,
c.三级分类编码,
c.三级分类名称,
c.三级分类层级,
d.四级分类编码,
d.四级分类名称,
d.四级分类层级,
e.五级分类编码,
e.五级分类名称,
e.五级分类层级