数据如下:
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.csdn.net/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 行受影响)
*/