求一个SQL排序,大家支持下
create table T(id varchar(50),tname varchar(50),tpath varchar(50),indextime datetime)

insert T

select '4b5d5056-58b7-4065-8ca9-21bd50c70c58','123456','087016','2011-12-16 09:48:01.810'
union all select '05527C13-3470-4CC0-AA75-A2512C407DC5','闪电买入价格以0.01微调','087016001','2011-12-19 09:06:08.855'
union all select '3BF1C178-627A-411C-B1B7-6E0D139868EC','鼠标悬停盘口价格以外的区域','087016002','2011-12-19 09:06:10.543'
union all select 'C77BB005-3E39-433D-B1D7-1D26E8C85186','锁定交易终端时，单击盘口弹出解锁窗体','087004','2011-12-16 09:47:56.309'
union all select 'D5CEAE3E-D0C1-4D9D-B55D-30482C0997D5','闪电交易窗体顶部Tab正确','087006','2011-12-16 09:47:57.809'
union all select 'B6357CF1-93E4-4053-AFBA-FEABF3FBF0F2','不登录交易终端单击盘口','087008','2011-12-16 09:48:33.466'
union all select '72809f19-72e0-4f0a-b562-c885199efbb2','8484','087009','2011-12-16 09:48:53.900'
union all select 'E0D1D497-A29E-4BE3-83FC-CA2D722D1195','闪电买入上市首日新股价格无限制','087011','2011-12-16 09:49:13.513'

select * from T

drop table T


排序要求为:树节点同一平级按indextime升序排,但先要排完某节点下面的所有子节点,再排下一个节点,上面测试数据的排序要求结果为:
id tname tpath indextime
------------------ -------------------------------------------------- -----------------------
C77BB005-3E39-433D-B1D7-1D26E8C85186 锁定交易终端时，单击盘口弹出解锁窗体 087004 2011-12-16 09:47:56.310
D5CEAE3E-D0C1-4D9D-B55D-30482C0997D5 闪电交易窗体顶部Tab正确 087006 2011-12-16 09:47:57.810
4b5d5056-58b7-4065-8ca9-21bd50c70c58 123456 087016 2011-12-19 09:06:08.855
05527C13-3470-4CC0-AA75-A2512C407DC5 闪电买入价格以0.01微调 087016001 2011-12-19 09:06:08.857
3BF1C178-627A-411C-B1B7-6E0D139868EC 鼠标悬停盘口价格以外的区域 087016002 2011-12-19 09:06:10.543
B6357CF1-93E4-4053-AFBA-FEABF3FBF0F2 不登录交易终端单击盘口 087008 2011-12-16 09:48:33.467
72809f19-72e0-4f0a-b562-c885199efbb2 8484 087009 2011-12-16 09:48:53.900
E0D1D497-A29E-4BE3-83FC-CA2D722D1195 闪电买入上市首日新股价格无限制 087011 2011-12-16 09:49:13.513

------解决方案--------------------

SQL code

BOM按节点排序应用实例 
--------------------------------------

--  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               休闲