数据库层级查询疑问

Question

本帖最后由 hizone911 于 2010-07-02 16:25:39 编辑

Answer 1

--注意红色部分
WITH SimpleRecursive(C_Name, PKID, C_Code,C_Parent)  
AS 
(
SELECT C_Name, PKID, C_Code,C_Parent  FROM CategorySelf WHERE C_Name = '分类1'  
UNION ALL 
SELECT p.C_Name, p.PKID, p.C_Code,p.C_parent  FROM CategorySelf  P  INNER JOIN SimpleRecursive A ON A.PKID = P.C_Parent
)  
SELECT sr.PKID,sr.C_Name as C_Name, c.C_Name as C_ParentName,sr.C_Code as C_ParentCode  
FROM SimpleRecursive sr left join CategorySelf c  
on sr.C_Parent=c.PKID ORDER BY sr.PKID
go

Answer 2

WITH SimpleRecursive(C_Name, PKID, C_Code,C_Parent)  
AS 
(
SELECT C_Name, PKID, C_Code,C_Parent  FROM CategorySelf WHERE C_Name = '分类1'  
UNION ALL 
SELECT p.C_Name, p.PKID, p.C_Code,p.C_parent  FROM CategorySelf  P  INNER JOIN SimpleRecursive A ON A.PKID = P.C_Parent
)  
SELECT sr.PKID,sr.C_Name as C_Name, c.C_Name as C_ParentName,sr.C_Code as C_ParentCode  
FROM SimpleRecursive sr left join CategorySelf c  
on sr.C_Parent=c.PKID ORDER BY sr.PKID
go
--结果：PKID        C_Name                                             C_ParentName                                       C_ParentCode
----------- -------------------------------------------------- -------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           分类1                                                NULL                                               0
4           分类4                                                分类1                                                1
6           分类6                                                分类4                                                1/4
7           分类7                                                分类4                                                1/4
9           分类9                                                分类6                                                1/4/6
10          分类10                                               分类6                                                1/4/6
11          分类11                                               分类6                                                1/4/6