一个难度很大的排序问题，求助!!!

[JiangHongTao]

id 为自身编号（自增长），pid 为父结点编号，porder 为在同一父结点下的排序号，sname 为地名
其实就是一棵多级树
create table #t (id int,pid int,poder int,sname varchar(100))
insert #t select 1,0,2,'中国'
union all select 2,0,1,'美国'
union all select 3,0,3,'英国'
union all select 4,1,2,'广东'
union all select 5,4,1,'深圳'
union all select 6,1,1,'上海'
union all select 7,4,2,'珠海'
union all select 8,0,4,'日本'
union all select 9,8,1,'东京'

请不要用函数和存储过程，最好是一条语句或几条语句搞定
要求输出：
id pid porder sname
2 0 1 '美国'
1 0 2 '中国'
6 1 1 '上海'
4 1 2 '广东'
5 4 1 '深圳'
7 4 2 '珠海'
3 0 3 '英国'
8 0 4 '日本'
9 8 1 '东京'

[liangCK]

转自邹建的blog

if exists (select * from dbo.sysobjects where id = object_id(N'[tb]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [tb]
GO

-- 示例数据
create table [tb]([id] int PRIMARY KEY,[pid] int,name nvarchar(20))
INSERT [tb] SELECT  1,0,N'中国'
UNION  ALL  SELECT  2,0,N'美国'
UNION  ALL  SELECT  3,0,N'加拿大'
UNION  ALL  SELECT  4,1,N'北京'
UNION  ALL  SELECT  5,1,N'上海'
UNION  ALL  SELECT  6,1,N'江苏'
UNION  ALL  SELECT  7,6,N'苏州'
UNION  ALL  SELECT  8,7,N'常熟'
UNION  ALL  SELECT  9,6,N'南京'
UNION  ALL  SELECT 10,6,N'无锡'
UNION  ALL  SELECT 11,2,N'纽约'
UNION  ALL  SELECT 12,2,N'旧金山'
GO 

-- 查询指定id的所有子 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_cid]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_cid]
GO
-- =====================================================
-- 查询指定id的所有子
-- 邹建 2005-07(引用请保留此信息)

-- 调用示例

/*--调用(查询所有的子)
    SELECT A.*,层次=B.[level] 
    FROM [tb] A,f_cid(2)B
    WHERE A.[id]=B.[id]
--*/
-- =====================================================
CREATE FUNCTION f_cid(@id int)
RETURNS TABLE
AS
RETURN(
    WITH ctb([id],[level])
    AS(
        SELECT [id],1 FROM [tb]
        WHERE [pid]=@id
        UNION ALL
        SELECT A.[id],B.[level]+1
        FROM [tb] A,ctb B
        WHERE A.[pid]=B.[id])
    SELECT * FROM ctb
    --如果只显示最明细的子(下面没有子),则将上面这句改为下面的
--    SELECT * FROM ctb A
--    WHERE NOT EXISTS(
--        SELECT 1 FROM [tb] WHERE [pid]=A.[id])
)
GO

--调用(查询所有的子)
SELECT A.*,层次=B.[level] 
FROM [tb] A,f_cid(2)B
WHERE A.[id]=B.[id]
GO 

-- 查询指定id的所有父 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_pid]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_pid]
GO
-- =====================================================
-- 查询指定id的所有父
-- 邹建 2005-07(引用请保留此信息)

-- 调用示例

/*--调用(查询所有的父)
    SELECT A.*,层次=B.[level] 
    FROM [tb] A,[f_pid](2)B
    WHERE A.[id]=B.[id]
--*/
-- =====================================================
CREATE FUNCTION [f_pid](@id int)
RETURNS TABLE
AS
RETURN(
    WITH ptb([id],[level])
    AS(
        SELECT [pid],1 FROM [tb]
        WHERE [id]=@id
            AND [pid]<>0
        UNION ALL
        SELECT A.[pid],B.[level]+1
        FROM [tb] A,ptb B
        WHERE A.[id]=B.[id]
            AND [pid]<>0)
    SELECT * FROM ptb
)
GO

--调用(查询所有的父)
SELECT A.*,层次=B.[level] 
FROM [tb] A,[f_pid](7)B
WHERE A.[id]=B.[id]
GO 

-- 树形分级显示 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_id]') and xtype in (N'FN', N'IF', N'TF'))
    drop function [dbo].[f_id]
GO
-- =====================================================
-- 级别及排序字段(树形分级显示)
-- 邹建 2005-07(引用请保留此信息)

-- 调用示例

/*--调用实现树形显示

    --调用函数实现分级显示
    SELECT N'|'+REPLICATE('-',B.[level]*4)+A.name
    FROM [tb] A,f_id()B 
    WHERE a.[id]=b.[id]
    ORDER BY b.sid

    --当然,这个也可以根本不用写函数,直接排序即可
    WITH stb([id],[level],[sid])
    AS(
        SELECT [id],1,CAST(RIGHT(10000+[id],4) as varchar(8000))
        FROM [tb]
        WHERE [pid]=0
        UNION ALL
        SELECT A.[id],B.[level]+1,B.sid+RIGHT(10000+A.[id],4)
        FROM [tb] A,stb B
        WHERE A.[pid]=B.[id])
    SELECT N'|'+REPLICATE('-',B.[level]*4)+A.name
    FROM [tb] A,stb B 
    WHERE a.[id]=b.[id]
    ORDER BY b.sid    
--*/
-- =====================================================
CREATE FUNCTION f_id()
RETURNS TABLE
AS
RETURN(
    WITH stb([id],[level],[sid])
    AS(
        SELECT [id],1,CAST(RIGHT(10000+[id],4) as varchar(8000))
        FROM [tb]
        WHERE [pid]=0
        UNION ALL
        SELECT A.[id],B.[level]+1,B.sid+RIGHT(10000+A.[id],4)
        FROM [tb] A,stb B
        WHERE A.[pid]=B.[id])
    SELECT * FROM stb
)
GO

--调用函数实现分级显示
SELECT N'|'+REPLICATE('-',B.[level]*4)+A.name
FROM [tb] A,f_id()B 
WHERE a.[id]=b.[id]
ORDER BY b.sid
GO 

[dobear_0922]

写个函数，可以搞定

[dobear_0922]

create table tb(id       int,pid       int,poder       int,sname       varchar(100))   
insert       tb       select       1,0,2,'中国'   
union       all       select       2,0,1,'美国'   
union       all       select       3,0,3,'英国'   
union       all       select       4,1,2,'广东'   
union       all       select       5,4,1,'深圳'   
union       all       select       6,1,1,'上海'   
union       all       select       7,4,2,'珠海'   
union       all       select       8,0,4,'日本'   
union       all       select       9,8,1,'东京'   

go
create function fn_Test(@id int)
returns varchar(8000) as
begin
	declare @ids varchar(8000)
	select @ids=','
	select @ids=@ids+rtrim(id)+dbo.fn_Test(id) from tb where pid=@id order by poder
	return @ids
end
go

declare @ids varchar(8000)
select @ids=','+dbo.fn_Test(0)
select @ids
select * from tb
order by charindex(','+rtrim(id)+',', @ids)

drop function dbo.fn_Test
drop table tb

/*
id          pid         poder       sname
----------- ----------- ----------- -----------------------
2           0           1           美国
1           0           2           中国
6           1           1           上海
4           1           2           广东
5           4           1           深圳
7           4           2           珠海
3           0           3           英国
8           0           4           日本
9           8           1           东京

(9 row(s) affected)
*/

[dawugui]

一条语句或几条搞不定.

[-狙击手-]

一句难

[dawugui]

我没看懂他排序的规则.

[yangjiexi]

--路过,看看!

[playwarcraft]

應該算是樹的深度排序，
還是用個存儲過程吧

[JiangHongTao]

谢谢3楼，比较经典。
但能否不用函数，我就是不想用递归函数和存储过程。
主要是存在移植问题。