按节点展开排序

数据如下：
OrderNo        ParentNo        Descript
2000007  2000004  老李孙子
2000006  2000001  老王儿子
2000005  2000002  老张儿子
2000004  2000000  老李儿子
2000003  NULL         NULL
2000002  NULL         老张
2000001  NULL         老王
2000000  NULL         老李

排序规则：
1，按OrderNo降序排
2，有相关联的订单要紧跟着排在后面

排序好以后应该如下：
OrderNo        ParentNo        Descript
2000007  2000004  老李孙子
2000004  2000000  老李儿子
2000000  NULL         老李
2000006  2000001  老王儿子
2000001  NULL         老王
2000005  2000002  老张儿子
2000002  NULL         老张
2000003  NULL         NULL

----------------------------------------------------------------------------------

-- Author : htl258(Tony)

-- Date   : 2009-04-15 11:40:00

-- 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 2)

-- Blog   : http://blog.youkuaiyun.com/htl258

----------------------------------------------------------------------------------

--> 生成测试数据表: [tb]

IF OBJECT_ID('[tb]') IS NOT NULL

    DROP TABLE [tb]

GO

CREATE TABLE [tb] ([OrderNo] [int],[ParentNo] [int],[Descript] [nvarchar](10))

INSERT INTO [tb]

SELECT '2000007','2000004','老李孙子' UNION ALL

SELECT '2000006','2000001','老王儿子' UNION ALL

SELECT '2000005','2000002','老张儿子' UNION ALL

SELECT '2000004','2000000','老李儿子' UNION ALL

SELECT '2000003',NULL,NULL UNION ALL

SELECT '2000002',NULL,'老张' UNION ALL

SELECT '2000001',NULL,'老王' UNION ALL

SELECT '2000000',NULL,'老李'

 

--SELECT * FROM [tb]

 

-->SQL查询如下:

;WITH t AS

(

    SELECT *, px = CAST(ROW_NUMBER()OVER(ORDER BY OrderNo DESC) AS VARBINARY)

    FROM tb a

    WHERE NOT EXISTS(

              SELECT 1

              FROM tb

              WHERE ParentNo = a.OrderNo

          )

    UNION ALL

    SELECT a.*, CAST(

               px+CAST(

                   ROW_NUMBER()OVER(PARTITION BY a.ParentNo ORDER BY a.OrderNo DESC) AS VARBINARY

               ) AS VARBINARY

           )

    FROM tb a

       JOIN t b

           ON  a.OrderNo = b.ParentNo

)

SELECT OrderNo,ParentNo,Descript

FROM t

ORDER BY px

/*

OrderNo     ParentNo    Descript

----------- ----------- ----------

2000007     2000004     老李孙子

2000004     2000000     老李儿子

2000000     NULL        老李

2000006     2000001     老王儿子

2000001     NULL        老王

2000005     2000002     老张儿子

2000002     NULL        老张

2000003     NULL        NULL

 

(8 行受影响)

*/

 

-------------libin_ftsafe

create table test(OrderNo varchar(10),ParentNo varchar(10),Descript varchar(10))
insert into test select '2000007','2000004','老李孙子'
insert into test select '2000006','2000001','老王儿子'
insert into test select '2000005','2000002','老张儿子'
insert into test select '2000004','2000000','老李儿子'
insert into test select '2000003',NULL     ,NULL
insert into test select '2000002',NULL     ,'老张'
insert into test select '2000001',NULL     ,'老王'
insert into test select '2000000',NULL     ,'老李'
go

create function f_str(@OrderNo varchar(10))
returns varchar(1000)
as
begin
    declare @ret varchar(1000),@ChildNo varchar(10)
    select @ret=OrderNo,@ChildNo=OrderNo from test where OrderNo=@OrderNo
   
    while @@rowcount<>0
    begin
        set @OrderNo=@ChildNo

        select @ret=OrderNo+@ret,@ChildNo=OrderNo from test where ParentNo=@OrderNo
    end

    return @ret+'Z'
end
go

select * from test order by dbo.f_str(OrderNo) desc
/*
OrderNo    ParentNo   Descript  
---------- ---------- ----------
2000007    2000004    老李孙子
2000004    2000000    老李儿子
2000000    NULL       老李
2000006    2000001    老王儿子
2000001    NULL       老王
2000005    2000002    老张儿子
2000002    NULL       老张
2000003    NULL       NULL
*/
go

drop function f_str
drop table test
go

-----------

---------------------------------
--  Author: liangCK 小梁
---------------------------------

--> 生成测试数据: [T]
IF OBJECT_ID('[T]') IS NOT NULL DROP TABLE [T]
CREATE TABLE [T] (OrderNo INT,ParentNo INT,Descript VARCHAR(8))
INSERT INTO [T]
SELECT 2000007,2000004,'老李孙子' UNION ALL
SELECT 2000006,2000001,'老王儿子' UNION ALL
SELECT 2000005,2000002,'老张儿子' UNION ALL
SELECT 2000004,2000000,'老李儿子' UNION ALL
SELECT 2000003,null,null UNION ALL
SELECT 2000002,null,'老张' UNION ALL
SELECT 2000001,null,'老王' UNION ALL
SELECT 2000000,null,'老李'

--SQL查询如下:

GO

CREATE FUNCTION dbo.sortOrderNo(@OrderNO INT)
    RETURNS VARCHAR(1000)
AS
BEGIN
    DECLARE @re VARCHAR(1000);
    SELECT @re=@OrderNo;
   
    SELECT
        @re=RTRIM(OrderNo)
    FROM T
    WHERE ParentNo = @OrderNo
   
    WHILE @@ROWCOUNT>0
       BEGIN
           SELECT
               @re=RTRIM(OrderNo)
           FROM T
           WHERE ParentNo = LEFT(@re,CHARINDEX(',',@re+',')-1);
       END
   
    RETURN @re
END
GO

SELECT * FROM T
ORDER BY dbo.sortOrderNo(OrderNo) DESC,OrderNo DESC

GO
DROP TABLE T
DROP FUNCTION dbo.sortOrderNo

/*
OrderNo     ParentNo    Descript
----------- ----------- --------
2000007     2000004     老李孙子
2000004     2000000     老李儿子
2000000     NULL        老李
2000006     2000001     老王儿子
2000001     NULL        老王
2000005     2000002     老张儿子
2000002     NULL        老张
2000003     NULL        NULL

(8 行受影响)

*/

 

本文来自优快云博客，转载请标明出处：http://blog.youkuaiyun.com/htl258/archive/2009/04/15/4075281.aspx