SQL排序,大家支持下_Sql Server

求一个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]GOCREATE TABLE [tb]([id] INT,[code] NVARCHAR(10),[pid] INT,[name] NVARCHAR(10))INSERT [tb]SELECT 1,'01',0,N'服装' UNION ALLSELECT 2,'01',1,N'男装' UNION ALLSELECT 3,'01',2,N'西装' UNION ALLSELECT 4,'01',3,N'全毛' UNION ALLSELECT 5,'02',3,N'化纤' UNION ALLSELECT 6,'02',2,N'休闲装' UNION ALLSELECT 7,'02',1,N'女装' UNION ALLSELECT 8,'01',7,N'套装' UNION ALLSELECT 9,'02',7,N'职业装' UNION ALLSELECT 10,'03',7,N'休闲装' UNION ALLSELECT 11,'04',7,N'西装' UNION ALLSELECT 12,'01',11,N'全毛' UNION ALLSELECT 13,'02',11,N'化纤' UNION ALLSELECT 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:--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL    DROP TABLE [tb]GOCREATE TABLE [tb]([id] INT,[parentid] INT,[categoryname] NVARCHAR(10))INSERT [tb]SELECT 1,0,'test1' UNION ALLSELECT 2,0,'test2' UNION ALLSELECT 3,1,'test1.1' UNION ALLSELECT 4,2,'test2.1' UNION ALLSELECT 5,3,'test1.1.1' UNION ALLSELECT 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           test13           1           test1.15           3           test1.1.16           1           test1.22           0           test24           2           test2.1(6 行受影响)*/本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/htl258/archive/2010/04/23/5518166.aspx
------解决方案--------------------
SQL codecreate table T(id varchar(50),tname varchar(50),tpath varchar(50),indextime datetime)insert Tselect '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 a  order by (select min(indextime) from t where LEFT(tpath,6)=LEFT(a.tpath,6))/*id                                                 tname                                              tpath                                              indextime-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -----------------------C77BB005-3E39-433D-B1D7-1D26E8C85186               锁定交易终端时，单击盘口弹出解锁窗体                                 087004                                             2011-12-16 09:47:56.310D5CEAE3E-D0C1-4D9D-B55D-30482C0997D5               闪电交易窗体顶部Tab正确                                      087006                                             2011-12-16 09:47:57.8104b5d5056-58b7-4065-8ca9-21bd50c70c58               123456                                             087016                                             2011-12-16 09:48:01.81005527C13-3470-4CC0-AA75-A2512C407DC5               闪电买入价格以0.01微调                                      087016001                                          2011-12-19 09:06:08.8573BF1C178-627A-411C-B1B7-6E0D139868EC               鼠标悬停盘口价格以外的区域                                      087016002                                          2011-12-19 09:06:10.543B6357CF1-93E4-4053-AFBA-FEABF3FBF0F2               不登录交易终端单击盘口                                        087008                                             2011-12-16 09:48:33.46772809f19-72e0-4f0a-b562-c885199efbb2               8484                                               087009                                             2011-12-16 09:48:53.900E0D1D497-A29E-4BE3-83FC-CA2D722D1195               闪电买入上市首日新股价格无限制                                    087011                                             2011-12-16 09:49:13.513(8 行受影响)
------解决方案--------------------
SQL codeDECLARE @T table (id varchar(50),tname varchar(50),tpath varchar(50),indextime datetime)insert @Tselect '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 AORDER BY (SELECT TOP 1 indextime FROM @T WHERE tpath = LEFT(A.tpath,3))  ,(SELECT TOP 1 indextime FROM @T WHERE tpath = LEFT(A.tpath,6))  ,indextime -- 结果id    tname    tpath    indextimeC77BB005-3E39-433D-B1D7-1D26E8C85186    锁定交易终端时，单击盘口弹出解锁窗体    087004    2011-12-16 09:47:56.310D5CEAE3E-D0C1-4D9D-B55D-30482C0997D5    闪电交易窗体顶部Tab正确    087006    2011-12-16 09:47:57.8104b5d5056-58b7-4065-8ca9-21bd50c70c58    123456    087016    2011-12-16 09:48:01.81005527C13-3470-4CC0-AA75-A2512C407DC5    闪电买入价格以0.01微调    087016001    2011-12-19 09:06:08.8573BF1C178-627A-411C-B1B7-6E0D139868EC    鼠标悬停盘口价格以外的区域    087016002    2011-12-19 09:06:10.543B6357CF1-93E4-4053-AFBA-FEABF3FBF0F2    不登录交易终端单击盘口    087008    2011-12-16 09:48:33.46772809f19-72e0-4f0a-b562-c885199efbb2    8484    087009    2011-12-16 09:48:53.900E0D1D497-A29E-4BE3-83FC-CA2D722D1195    闪电买入上市首日新股价格无限制    087011    2011-12-16 09:49:13.513
------解决方案--------------------
如果楼主的上下级关系仅仅为:第6位是根,其子及孙分别为第5位和第4位,再下去看第7位及以下的从左向右的顺序,那可以这样:
SQL codecreate table T(id varchar(50),tname varchar(50),tpath varchar(50),indextime datetime)insert Tselect '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'goselect * from T order by STUFF(tpath,4,3,reverse(substring(tpath,4,3))),indextime/*id                                                 tname                                              tpath                                              indextime-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -----------------------E0D1D497-A29E-4BE3-83FC-CA2D722D1195               闪电买入上市首日新股价格无限制                                    087011                                             2011-12-16 09:49:13.513C77BB005-3E39-433D-B1D7-1D26E8C85186               锁定交易终端时，单击盘口弹出解锁窗体                                 087004                                             2011-12-16 09:47:56.310D5CEAE3E-D0C1-4D9D-B55D-30482C0997D5               闪电交易窗体顶部Tab正确                                      087006                                             2011-12-16 09:47:57.8104b5d5056-58b7-4065-8ca9-21bd50c70c58               123456                                             087016                                             2011-12-16 09:48:01.81005527C13-3470-4CC0-AA75-A2512C407DC5               闪电买入价格以0.01微调                                      087016001                                          2011-12-19 09:06:08.8573BF1C178-627A-411C-B1B7-6E0D139868EC               鼠标悬停盘口价格以外的区域                                      087016002                                          2011-12-19 09:06:10.543B6357CF1-93E4-4053-AFBA-FEABF3FBF0F2               不登录交易终端单击盘口                                        087008                                             2011-12-16 09:48:33.46772809f19-72e0-4f0a-b562-c885199efbb2               8484                                               087009                                             2011-12-16 09:48:53.900(8 行受影响)*/godrop table T