根据ParentId排序，简单实用

--第一步：创建表
drop table OrderTable
create table OrderTable(Id int identity,Name varchar(20),ParentId int)

--第二步：插入模拟数据
--顶级ParentId要置为null
insert into OrderTable values('部门1',null)
insert into OrderTable values('部门2',null)
insert into OrderTable values('部门1-1',1)
insert into OrderTable values('部门1-2',1)
insert into OrderTable values('部门2-1',2)
insert into OrderTable values('部门2-2',2)
insert into OrderTable values('部门1-1-1',3)
insert into OrderTable values('部门1-1-2',3)

--第三步：查询数据
select * from OrderTable

1 部门1  NULL
2 部门2  NULL
3 部门1-1  1
4 部门1-2  1
5 部门2-1  2
6 部门2-2  2
7 部门1-1-1 3
8 部门1-1-2 3

--第四部：创建存储过程
alter function [dbo].[fn_GetOrder]( @Id int)
returns varchar(20)
as
/*==============================================================*/
/* Create Date: 2012-11-06                                       */
/* Description: 根据OrderTable.Id返回该组织的'排序号'       */
/* CallExample: select dbo.fn_GetOrder(Id) as Order, * from OrderTable order by dbo.fn_GetOrder(Id)*/
/*==============================================================*/
begin
 declare @ParentId int
 declare @Order varchar(20)
 set @Order = replicate('0', 4-len(convert(varchar,@Id))) + convert(varchar,@Id)
 select @ParentId = ParentId from OrderTable where Id = @Id
 
 while @ParentId is not null
 begin
  set @Order = replicate('0', 4-len(convert(varchar,@ParentId))) + convert(varchar,@ParentId) + @Order
  select @ParentId = ParentId from OrderTable where Id = @ParentId 
 end 
 return @Order
end

--第五步：查询所有数据
select *, dbo.fn_GetOrder(Id) as Orders from OrderTable order by dbo.fn_GetOrder(Id)

1 部门1  NULL 0001
3 部门1-1  1  00010003
7 部门1-1-1 3  000100030007
8 部门1-1-2 3  000100030008
4 部门1-2  1  00010004
2 部门2  NULL 0002
5 部门2-1  2  00020005
6 部门2-2  2  00020006

--第六步：查询所有子数据
select *, dbo.fn_GetOrder(Id) as Orders
from OrderTable
where dbo.fn_GetOrder(Id) like dbo.fn_GetOrder(1)+'%'
order by dbo.fn_GetOrder(Id)

1 部门1  NULL 0001
3 部门1-1  1  00010003
7 部门1-1-1 3  000100030007
8 部门1-1-2 3  000100030008
4 部门1-2  1  00010004