当前位置：首页 → 问答吧 → 求一条排序的语句。动态层级排序

求一条排序的语句。动态层级排序

时间：2011-11-09

来源：互联网

想实现最后的排序结果，PID为上级的ID,这个层次不固定，是动态的，举例使用了3层。
SQL code


CREATE TABLE #T(ID INT,N VARCHAR(10),PID INT)
INSERT INTO #T VALUES(1,'A',NULL)
INSERT INTO #T VALUES(2,'B',NULL)
INSERT INTO #T VALUES(3,'C',NULL)
INSERT INTO #T VALUES(4,'AA',1)
INSERT INTO #T VALUES(5,'AB',1)
INSERT INTO #T VALUES(6,'AC',1)
INSERT INTO #T VALUES(7,'BA',2)
INSERT INTO #T VALUES(8,'BB',2)
INSERT INTO #T VALUES(9,'BC',2)
INSERT INTO #T VALUES(10,'CA',3)
INSERT INTO #T VALUES (11,'AAA',4)
--想实现的排序结果
A
AA
AAA
AB
AC
B
BA
BB
BC
C
CA

作者: shuchong1983 发布时间: 2011-11-09

如果表设计的规范点就好排了

1001
1002
1001001
...........
这样的

作者: Beirut 发布时间: 2011-11-09

SQL code

ORDER BY N

--數據是否正確

作者: roy_88 发布时间: 2011-11-09

引用 2 楼 roy_88 的回复:

SQL code
ORDER BY N
--數據是否正確

嗯，如果这么有规律那就 order by n

作者: Beirut 发布时间: 2011-11-09

SQL code

CREATE TABLE #T(ID INT,N VARCHAR(10),PID INT)
INSERT INTO #T VALUES(1,'A',NULL)
INSERT INTO #T VALUES(2,'B',NULL)
INSERT INTO #T VALUES(3,'C',NULL)
INSERT INTO #T VALUES(4,'AA',1)
INSERT INTO #T VALUES(5,'AB',1)
INSERT INTO #T VALUES(6,'AC',1)
INSERT INTO #T VALUES(7,'BA',2)
INSERT INTO #T VALUES(8,'BB',2)
INSERT INTO #T VALUES(9,'BC',2)
INSERT INTO #T VALUES(10,'CA',3)
INSERT INTO #T VALUES (11,'AAA',4)

;WITH a
AS
(
SELECT *,ord=CAST(RIGHT(1000+ID,3) AS NVARCHAR(200)) FROM #T AS a WHERE  NOT EXISTS(SELECT 1 FROM #T WHERE ID=a.PID)
UNION ALL
SELECT b.*,CAST(a.ord+ RIGHT(1000+b.ID,3)AS NVARCHAR(200)) FROM a INNER JOIN #T AS b ON b.PID=a.ID
)
SELECT * FROM a ORDER BY ord

/*
ID    N    PID    ord
1    A    NULL    001
4    AA    1    001004
11    AAA    4    001004011
5    AB    1    001005
6    AC    1    001006
2    B    NULL    002
7    BA    2    002007
8    BB    2    002008
9    BC    2    002009
3    C    NULL    003
10    CA    3    003010
*/
DROP TABLE #T

作者: roy_88 发布时间: 2011-11-09

SQL code

CREATE TABLE #T(ID INT,N VARCHAR(10),PID INT)
INSERT INTO #T VALUES(1,'A',NULL)
INSERT INTO #T VALUES(2,'B',NULL)
INSERT INTO #T VALUES(3,'C',NULL)
INSERT INTO #T VALUES(4,'AA',1)
INSERT INTO #T VALUES(5,'AB',1)
INSERT INTO #T VALUES(6,'AC',1)
INSERT INTO #T VALUES(7,'BA',2)
INSERT INTO #T VALUES(8,'BB',2)
INSERT INTO #T VALUES(9,'BC',2)
INSERT INTO #T VALUES(10,'CA',3)
INSERT INTO #T VALUES (11,'AAA',4)

;WITH a
AS
(
SELECT *,ord=CAST(RIGHT(1000+ID,3) AS NVARCHAR(200)) FROM #T AS a WHERE  NOT EXISTS(SELECT 1 FROM #T WHERE ID=a.PID)
UNION ALL
SELECT b.*,CAST(a.ord+ RIGHT(1000+b.ID,3)AS NVARCHAR(200)) FROM a INNER JOIN #T AS b ON b.PID=a.ID
)
SELECT ID,N,PID FROM a ORDER BY ord

/*
ID    N    PID
1    A    NULL
4    AA    1
11    AAA    4
5    AB    1
6    AC    1
2    B    NULL
7    BA    2
8    BB    2
9    BC    2
3    C    NULL
10    CA    3
*/
DROP TABLE #T

作者: roy_88 发布时间: 2011-11-09

用遞歸生成一個排序列

作者: roy_88 发布时间: 2011-11-09

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

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 行受影响)
*/



本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/htl258/archive/2010/04/23/5518166.aspx