sql数据库按树形结构排序查询表记录

WITH T AS 
( 
    SELECT CAST(Tree_Id AS VARCHAR(20)) AS CODE,*,CAST(City_Id AS VARBINARY(MAX)) AS px  FROM Class_City AS A 
    WHERE NOT EXISTS(SELECT * FROM Class_City WHERE City_Id=A.Father_Id) 
    UNION ALL  
    SELECT CAST(B.Tree_Id+A.Tree_Id AS VARCHAR(20)),A.*,CAST(B.px+CAST(A.City_Id AS VARBINARY) AS VARBINARY(MAX))  FROM Class_City AS A 
    JOIN T AS B ON A.Father_Id=B.City_Id 
) 
 
SELECT City_Id,HelpCode,City_Name,Class_Class,Father_Id FROM T  
ORDER BY px 

-------------------------------------------------------------------------- 
--  Author : htl258(Tony) 
--  Date   : 2010-04-23 02:37:28 
--  Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)  
--          Jul  9 2008 14:43:34  
--          Copyright (c) 1988-2008 Microsoft Corporation 
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3) 
--  Subject: BOM按节点排序应用实例 
-------------------------------------------------------------------------- 
--实例1: 
--> 生成测试数据表:tb 
IF NOT OBJECT_ID('[tb]') IS NULL 
    DROP TABLE [tb] 
GO 
 
CREATE TABLE [tb]([id] INT,[code] NVARCHAR(10),[pid] INT,[name] NVARCHAR(10)) 
INSERT [tb] 
SELECT 1,'01',0,N'服装' UNION ALL 
SELECT 2,'01',1,N'男装' UNION ALL 
SELECT 3,'01',2,N'西装' UNION ALL 
SELECT 4,'01',3,N'全毛' UNION ALL 
SELECT 5,'02',3,N'化纤' UNION ALL 
SELECT 6,'02',2,N'休闲装' UNION ALL 
SELECT 7,'02',1,N'女装' UNION ALL 
SELECT 8,'01',7,N'套装' UNION ALL 
SELECT 9,'02',7,N'职业装' UNION ALL 
SELECT 10,'03',7,N'休闲装' UNION ALL 
SELECT 11,'04',7,N'西装' UNION ALL 
SELECT 12,'01',11,N'全毛' UNION ALL 
SELECT 13,'02',11,N'化纤' UNION ALL 
SELECT 14,'05',7,N'休闲装' 
GO 
 
--SELECT * FROM [tb] 
-->SQL查询如下: 
;WITH T AS 
( 
    SELECT CAST(CODE AS VARCHAR(20)) AS CODE,*, 
        CAST(ID AS VARBINARY(MAX)) AS px  
    FROM tb AS A 
    WHERE NOT EXISTS(SELECT * FROM tb WHERE id=A.pid) 
    UNION ALL  
    SELECT CAST(B.CODE+A.CODE AS VARCHAR(20)),A.*, 
         CAST(B.px+CAST(A.ID AS VARBINARY) AS VARBINARY(MAX))     
    FROM tb AS A 
        JOIN T AS B 
           ON A.pid=B.id 
) 
 
SELECT Code,Name FROM T  
ORDER BY px 
/* 
Code                 Name 
-------------------- ---------- 
01                   服装 
0101                 男装 
010101               西装 
01010101             全毛 
01010102             化纤 
010102               休闲装 
0102                 女装 
010201               套装 
010202               职业装 
010203               休闲装 
010204               西装 
01020401             全毛 
01020402             化纤 
010205               休闲装 
(14 行受影响) 
*/ 
  
--实例2: 
--> 生成测试数据表:tb 
 
IF NOT OBJECT_ID('[tb]') IS NULL 
    DROP TABLE [tb] 
GO 
CREATE TABLE [tb]([id] INT,[parentid] INT,[categoryname] NVARCHAR(10)) 
INSERT [tb] 
SELECT 1,0,'test1' UNION ALL 
SELECT 2,0,'test2' UNION ALL 
SELECT 3,1,'test1.1' UNION ALL 
SELECT 4,2,'test2.1' UNION ALL 
SELECT 5,3,'test1.1.1' UNION ALL 
SELECT 6,1,'test1.2' 
GO 
--SELECT * FROM [tb] 
 
-->SQL查询如下: 
;WITH T AS 
( 
    SELECT *,CAST(ID AS VARBINARY(MAX)) AS px  
    FROM tb AS A 
    WHERE NOT EXISTS(SELECT * FROM tb WHERE id=A.[parentid]) 
    UNION ALL  
    SELECT A.*,CAST(B.px+CAST(A.ID AS VARBINARY) AS VARBINARY(MAX))   
    FROM tb AS A 
        JOIN T AS B 
           ON A.[parentid]=B.id 
) 
SELECT [id],[parentid],[categoryname] FROM T  
ORDER BY px 
/* 
id          parentid    categoryname 
----------- ----------- ------------ 
1           0           test1 
3           1           test1.1 
5           3           test1.1.1 
6           1           test1.2 
2           0           test2 
4           2           test2.1 
 
(6 行受影响) 
*/