ms-sql排序的方法,该怎么解决

ms-sql排序的方法
有表T 其中ALLID字段为字符串
ALLID的内容
|A||A||C|
|A||C|
|B||A|
|A||B|
|A|

如何用order by排序实现下面的效果
|A|
|A||A||C|
|A||B|
|A||C|
|B||A|

[解决办法]

SQL code

--> 生成测试数据表: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.1BOM节点排序，看你的数据应该是这个意思，稍微修改即可
[解决办法]
SQL code
--实例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 行受影响)*/
------解决方案-------------------- 
 
SQL code
declare @t table (ALLID char(12))insert into @tselect '|A||A||C|' union allselect '|A||C|' union allselect '|B||A|' union allselect '|A||B|' union allselect '|A|'select * from @t order by substring(ALLID,0,3),substring(ALLID,3,6)/*ALLID------------|A|         |A||A||C|   |A||B|      |A||C|      |B||A|   */
[解决办法]
探讨


引用:

SQL code

declare @t table (ALLID char(12))
insert into @t
select '|A||A||C|' union all
select '|A||C|' union all
select '|B||A|' union all
select '|A||B|' union all
……