--lz给的示例有问题if object_id('testtable') is not null drop table testtable
go
create table testtable
(
fid int,
fparentid int
)
insert into testtable
select 1,0 union all
select 2,0 union all
select 3,1 union all
select 4,2 union all
select 5,3
go
if object_id('f_test') is not null drop function f_test
go
create function f_test(@a int)
returns int
as
begin
declare @r int
set @r=@a
while not exists (select 1 from testtable where fparentid=0 and fid=@r)
and exists (select 1 from testtable where fid=@r)
select @r=fparentid from testtable where fid=@r
return @r
end
go
select *
from testtable
order by dbo.f_test(fid)/*
fid fparentid
----------- -----------
1 0
3 1
5 3
4 2
2 0
*/
go
create table testtable
(
fid int,
fparentid int
)
insert into testtable
select 1,0 union all
select 2,0 union all
select 3,1 union all
select 4,2 union all
select 5,3
go
if object_id('f_test') is not null drop function f_test
go
create function f_test(@a int)
returns int
as
begin
declare @r int
set @r=@a
while not exists (select 1 from testtable where fparentid=0 and fid=@r)
and exists (select 1 from testtable where fid=@r)
select @r=fparentid from testtable where fid=@r
return @r
end
go
select *
from testtable
order by dbo.f_test(fid)/*
fid fparentid
----------- -----------
1 0
3 1
5 3
4 2
2 0
*/
解决方案 »
- 不小心把某张表删除后,为什么不能还原?
- 关于insert的疑问!!
- 一个表中有入库记录和出库记录,用收、发字段来标示,如何求余额
- sql server2008 求解
- USE a51118101 Exec sp_addrolemember db_owner,'a51118101'
- 急求:请问我在企业管理里把数据库删除掉了,怎么恢复呀?我没有任何备份?:(
- 字段数据类型问题
- 一个表的主键为Varchar类型,加入数据的问题.
- The job failed.why?
- 关于分区视图及查询优化的问题
- SQL SERVER 是否可以自动在每天晚上23:59:59执行一个指定的应用程序
- 帮忙解决一个四张表关联的删除语句
go
create table testtable
(
fid int,
fparentid int
)
insert into testtable
select 1,0 union all
select 2,0 union all
select 3,1 union all
select 4,2 union all
select 5,3
go
if object_id('f_test') is not null drop function f_test
go
create function f_test(@a int)
returns int
as
begin
declare @r int
set @r=@a
while not exists (select 1 from testtable where fparentid=0 and fid=@r)
and exists (select 1 from testtable where fid=@r)
select @r=fparentid from testtable where fid=@r
return @r
end
go
select *
from testtable
order by dbo.f_test(fid),fparentid/*
fid fparentid
----------- -----------
1 0
3 1
5 3
2 0
4 2
*/
CREATE TABLE tb(fid int, fparentid int)
INSERT tb SELECT 1, 0
UNION ALL SELECT 2, 0
UNION ALL SELECT 3, 1
UNION ALL SELECT 4, 2
UNION ALL SELECT 5, 3
GO-- 生成 path 的函数
CREATE FUNCTION dbo.f_path(
@fid int
) RETURNS varchar(8000)
AS
BEGIN
DECLARE @re varchar(8000)
SELECT @re = RIGHT(10000 + fid, 4), @fid = fparentid
FROM tb
WHERE fid = @fid
WHILE @@ROWCOUNT > 0
SELECT @re = RIGHT(10000 + fid, 4) + @re, @fid = fparentid
FROM tb
WHERE fid = @fid
RETURN(@re)
END
GO-- 调用函数实现排序
SELECT * FROM tb
ORDER BY dbo.f_path(fid)
GO-- 删除测试
DROP TABLE tb
DROP FUNCTION dbo.f_path/*--测试结果
fid fparentid
----------- -----------
1 0
3 1
5 3
2 0
4 2(5 行受影响)--*/