LouisXIV(夜游神) 请进...............................
create table test
(
ID int,
classname varchar(10),
parentID int
)
insert into test
select 1,'中国',0 union all
select 2,'上海',1 union all
select 3,'江西',1 union all
select 4,'浙江',1 union all
select 5,'江苏',1 union all
select 6,'南昌',3 union all
select 7,'杭州市',4 union all
select 8,'九江',3 union all
select 9,'温州',4
go
create function f_root(@a int)
returns int
as
begin
declare @pid int
declare @return varchar(10)
select @pid=@a
select @return=''
while @pid>=1
begin
select @return=rtrim(@pid)+@return
select @pid=parentid from test where id=@a
select @a=@pid
end
return @return
end
go
select id,classname
from test
order by cast(dbo.f_root(id)as varchar)
/*
1 中国
2 上海
3 江西
6 南昌
8 九江
4 浙江
7 杭州市
9 温州
5 江苏
*/
这个还是有问题无通用性,我再加了几个大类，比如国家．显示就不对
比如我改成这样子，显示出来的就不对了
create table test
(
ID int,
classname varchar(10),
parentID int
)
insert into test
select 1,'中国',0 union all
select 2,'上海',1 union all
select 3,'江西',1 union all
select 4,'浙江',1 union all
select 5,'江苏',1 union all
select 6,'南昌',3 union all
select 7,'杭州市',4 union all
select 8,'九江',3 union all
select 9,'温州',4 union all
select 10,'英国'0 union all
select 11,'法国'0 union all
go
create function f_root(@a int)
returns int
as
begin
declare @pid int
declare @return varchar(10)
select @pid=@a
select @return=''
while @pid>=1
begin
select @return=rtrim(@pid)+@return
select @pid=parentid from test where id=@a
select @a=@pid
end
return @return
end
go
select id,classname
from test
order by cast(dbo.f_root(id)as varchar)
问题点数：20、回复次数：2Top
1 楼paoluo（一天到晚游泳的鱼）回复于 2006-07-04 15:45:13 得分 10
修改一下即可。
create function f_root(@a int)
returns varchar(10)
as
begin
declare @pid int
declare @return varchar(1000)
select @pid=@a
select @return=''
while @pid>=1
begin
select @return=rtrim(@pid)+','+@return
select @pid=parentid from test where id=@a
select @a=@pid
end
return @return
end
go
select id,classname, dbo.f_root(id)
from test
order by dbo.f_root(id)Top
2 楼paoluo（一天到晚游泳的鱼）回复于 2006-07-04 15:46:29 得分 10
create table test
(
ID int,
classname Nvarchar(10),
parentID int
)
insert into test
select 1,N'中国',0 union all
select 2,N'上海',1 union all
select 3,N'江西',1 union all
select 4,N'浙江',1 union all
select 5,N'江苏',1 union all
select 6,N'南昌',3 union all
select 7,N'杭州市',4 union all
select 8,N'九江',3 union all
select 9,N'温州',4 union all
select 10,N'英国',0 union all
select 11,N'法国',0
go
create function f_root(@a int)
returns varchar(10)
as
begin
declare @pid int
declare @return varchar(1000)
select @pid=@a
select @return=''
while @pid>=1
begin
select @return=rtrim(@pid)+','+@return
select @pid=parentid from test where id=@a
select @a=@pid
end
return @return
end
go
select id,classname
from test
order by dbo.f_root(id)
GO
Drop function f_root
Drop table test
--Result
/*
id classname
1 中国
2 上海
3 江西
6 南昌
8 九江
4 浙江
7 杭州市
9 温州
5 江苏
10 英国
11 法国
*/Top
