有一个表:id re cdate srid
1 aa 2006-01-01
2 bb 2007-01-02 1
3 cc 2006-01-03
4 ee 2006-01-08 2
5 gg 2006-01-07 3
6 ff 2006-05-04 5
如果显示:
id re cdate srid
1 aa 2006-01-01
2 bb 2007-01-02 1
4 ee 2006-01-08 2或者显示:
3 cc 2006-01-03
5 gg 2006-01-07 3
6 ff 2006-05-04 5
id与srid的关系是承上的关系,怎么做?
1 aa 2006-01-01
2 bb 2007-01-02 1
3 cc 2006-01-03
4 ee 2006-01-08 2
5 gg 2006-01-07 3
6 ff 2006-05-04 5
如果显示:
id re cdate srid
1 aa 2006-01-01
2 bb 2007-01-02 1
4 ee 2006-01-08 2或者显示:
3 cc 2006-01-03
5 gg 2006-01-07 3
6 ff 2006-05-04 5
id与srid的关系是承上的关系,怎么做?
insert into t select 1,'aa','2006-01-01',null
insert into t select 2,'bb','2007-01-02',1
insert into t select 3,'cc','2006-01-03',null
insert into t select 4,'ee','2006-01-08',2
insert into t select 5,'gg','2006-01-07',3
insert into t select 6,'ff','2006-05-04',5
go
create function f_getParent(@id int)
returns int
as
begin
while exists(select 1 from t where id=@id)
select @id=srid from t where id=@id return @id
end
go
select
a.*
from
t a,
(select top 1 id from t where srid is null order by newid()) b
where
dbo.f_getParent(a.id)=b.id
go
drop function f_getParent
drop table t
insert into t select 1,'aa','2006-01-01',null
insert into t select 2,'bb','2007-01-02',1
insert into t select 3,'cc','2006-01-03',null
insert into t select 4,'ee','2006-01-08',2
insert into t select 5,'gg','2006-01-07',3
insert into t select 6,'ff','2006-05-04',5
go
create function f_getParent(@id int)
returns int
as
begin
while exists(select 1 from t where id=@id and srid is not null)
select @id=srid from t where id=@id return @id
end
go
select
a.*
from
t a,
(select top 1 id from t where srid is null order by newid()) b
where
dbo.f_getParent(a.id)=b.id
go
drop function f_getParent
drop table t
insert into t select 1,'aa','2006-01-01',null
insert into t select 2,'bb','2007-01-02',1
insert into t select 3,'cc','2006-01-03',null
insert into t select 4,'ee','2006-01-08',2
insert into t select 5,'gg','2006-01-07',3
insert into t select 6,'ff','2006-05-04',5
go-- 查询处理
DECLARE @id int
SET @id = 1 -- 要查询的ID;WITH tree
AS(
SELECT * FROM t WHERE id = @id
UNION ALL
SELECT t.* FROM t, tree t1
WHERE t.srid = t1.id
)
SELECT * FROM tree
GOdrop table t-- 结果:
id re cdate srid
----------- ------ ----------------------- -----------
1 aa 2006-01-01 00:00:00.000 NULL
2 bb 2007-01-02 00:00:00.000 1
4 ee 2006-01-08 00:00:00.000 2(3 行受影响)