drop table tbtest go create table tbtest(id int,fid int,name varchar(10)) insert into tbtest select 1,0,'A' union all select 2,0,'B' union all select 3,0,'C' union all select 4,3,'CC' union all select 5,2,'BB' union all select 6,1,'AA' union all select 7,6,'AAA' select id,fid,name from tbtest order by name
--结果 id fid name ----------- ----------- ---------- 1 0 A 6 1 AA 7 6 AAA 2 0 B 5 2 BB 3 0 C 4 3 CC(所影响的行数为 7 行)
select * from tt order by name,ID
select * from tt order by name-----看样子好像是个树,不知道lz是不是这个意思。ls说太奢侈了,那我也来凑个热闹
if object_id('pubs..tb') is not null drop table tb gocreate table tb(ID int ,FID int , Name varchar(10)) insert into tb(ID,FID,Name) values(1, 0, 'A') insert into tb(ID,FID,Name) values(2, 0, 'B') insert into tb(ID,FID,Name) values(3, 0, 'C' ) insert into tb(ID,FID,Name) values(4, 3, 'CC') insert into tb(ID,FID,Name) values(5, 2, 'BB' ) insert into tb(ID,FID,Name) values(6, 1, 'AA' ) insert into tb(ID,FID,Name) values(7, 6, 'AAA' ) goselect * from tb order by namedrop table tb/* ID FID Name ----------- ----------- ---------- 1 0 A 6 1 AA 7 6 AAA 2 0 B 5 2 BB 3 0 C 4 3 CC(所影响的行数为 7 行)*/
create table TT(ID int,FID int,Name varchar(10)) insert into TT select 1,0,'A' insert into TT select 2,0,'B' insert into TT select 3,0,'C' insert into TT select 4,3,'CC' insert into TT select 5,2,'BB' insert into TT select 6,1,'AA' insert into TT select 7,6,'AAA' go--创建用户定义函数 create function f_getNum(@ID int) returns varchar(4000) as begin declare @ret varchar(4000),@FID int set @ret = right('0000'+rtrim(@ID),4) while exists(select 1 from TT where ID=@ID and FID!=0) begin select @FID=FID from TT where ID=@ID and FID!=0 set @ID = @FID set @ret = right('0000'+rtrim(@id),4)+@ret end return @ret end goselect * from TT order by dbo.f_getNum(id) /* ID FID Name ----------- ----------- ---------- 1 0 A 6 1 AA 7 6 AAA 2 0 B 5 2 BB 3 0 C 4 3 CC */ godrop function f_getNum drop table TT go
go
create table tbtest(id int,fid int,name varchar(10))
insert into tbtest
select 1,0,'A'
union all select 2,0,'B'
union all select 3,0,'C'
union all select 4,3,'CC'
union all select 5,2,'BB'
union all select 6,1,'AA'
union all select 7,6,'AAA'
select id,fid,name from tbtest order by name
id fid name
----------- ----------- ----------
1 0 A
6 1 AA
7 6 AAA
2 0 B
5 2 BB
3 0 C
4 3 CC(所影响的行数为 7 行)
drop table tb
gocreate table tb(ID int ,FID int , Name varchar(10))
insert into tb(ID,FID,Name) values(1, 0, 'A')
insert into tb(ID,FID,Name) values(2, 0, 'B')
insert into tb(ID,FID,Name) values(3, 0, 'C' )
insert into tb(ID,FID,Name) values(4, 3, 'CC')
insert into tb(ID,FID,Name) values(5, 2, 'BB' )
insert into tb(ID,FID,Name) values(6, 1, 'AA' )
insert into tb(ID,FID,Name) values(7, 6, 'AAA' )
goselect * from tb order by namedrop table tb/*
ID FID Name
----------- ----------- ----------
1 0 A
6 1 AA
7 6 AAA
2 0 B
5 2 BB
3 0 C
4 3 CC(所影响的行数为 7 行)*/
insert into TT select 1,0,'A'
insert into TT select 2,0,'B'
insert into TT select 3,0,'C'
insert into TT select 4,3,'CC'
insert into TT select 5,2,'BB'
insert into TT select 6,1,'AA'
insert into TT select 7,6,'AAA'
go--创建用户定义函数
create function f_getNum(@ID int)
returns varchar(4000)
as
begin
declare @ret varchar(4000),@FID int
set @ret = right('0000'+rtrim(@ID),4)
while exists(select 1 from TT where ID=@ID and FID!=0)
begin
select @FID=FID from TT where ID=@ID and FID!=0
set @ID = @FID
set @ret = right('0000'+rtrim(@id),4)+@ret
end
return @ret
end
goselect * from TT order by dbo.f_getNum(id)
/*
ID FID Name
----------- ----------- ----------
1 0 A
6 1 AA
7 6 AAA
2 0 B
5 2 BB
3 0 C
4 3 CC
*/
godrop function f_getNum
drop table TT
go
from 表
order by name