34,593
社区成员
发帖
与我相关
我的任务
分享
declare @t table(F_CHILD_ID int,F_ACT_ID int)
insert into @t
select 4, 1
union all select 4, 1
union all select 4, 1
union all select 1, 2
union all select 3, 4
if object_id('tempdb..#')is not null
drop table #
select * into #
from @t
alter table # add id int identity(1,1)
if object_id('tempdb..#1')is not null
drop table #1
go
select
a.*
,bid = b.id
into #1
from # a
left join # b
on a.f_act_id = b.f_child_id
order by b.f_child_id
select *
from #1 a
where not exists(select 1 from #1 where id = a.id and bid < a.bid)
F_CHILD_ID F_ACT_ID id bid
----------- ----------- ----------- -----------
1 2 4 NULL
4 1 1 4
4 1 2 4
4 1 3 4
3 4 5 1
(所影响的行数为 5 行)
create table t1(F_CHILD_ID int,F_ACT_ID int)
insert into t1 select 4,1
insert into t1 select 4,1
insert into t1 select 4,1
insert into t1 select 1,2
insert into t1 select 3,4
go
--创建用户定义函数
create function f_getNum(@F_ACT_ID int)
returns varchar(4000)
as
begin
declare @ret varchar(4000),@F_CHILD_ID int
set @ret = right('0000'+rtrim(@F_ACT_ID),4)
while exists(select 1 from t1 where F_CHILD_ID=@F_ACT_ID)
begin
select @F_CHILD_ID=F_ACT_ID from t1 where F_CHILD_ID=@F_ACT_ID
set @F_ACT_ID = @F_CHILD_ID
set @ret = right('0000'+rtrim(@F_ACT_ID),4)+@ret
end
return @ret
end
go
--执行查询
select
a.F_CHILD_ID,
a.F_ACT_ID
from
t1 a
order by
dbo.f_getNum(a.F_ACT_ID)
/*
F_CHILD_ID F_ACT_ID
----------- -----------
1 2
4 1
4 1
4 1
3 4
*/
go
drop function f_getNum
drop table t1
go
/*---------------------------------
-- Author : htl258(Tony)
-- Date : 2009-09-15 18:45:44
-- Version: Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
---------------------------------*/
--> 生成测试数据表:tb
IF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([F_CHILD_ID] INT,[F_ACT_ID] INT)
INSERT [tb]
SELECT 4,1 UNION ALL
SELECT 4,1 UNION ALL
SELECT 4,1 UNION ALL
SELECT 1,2 UNION ALL
SELECT 3,4
GO
--SELECT * FROM [tb]
-->SQL查询如下:
declare @t table(F_SORT_ID int identity,[F_CHILD_ID] int,[F_ACT_ID] INT,lvl int)
declare @lvl int
set @lvl=0
insert @t select *,@lvl from tb t where not exists(select 1 from tb where F_CHILD_ID=t.F_ACT_ID)
while @@rowcount>0
begin
set @lvl=@lvl+1
insert @t select *,@lvl from tb a where exists(select 1 from @t where a.F_ACT_ID=F_CHILD_ID and lvl=@lvl-1)
end
select F_SORT_ID,F_CHILD_ID,F_ACT_ID from @t
/*
F_SORT_ID F_CHILD_ID F_ACT_ID
----------- ----------- -----------
1 1 2
2 4 1
3 4 1
4 4 1
5 3 4
(所影响的行数为 5 行)
*/