-->测试数据 if object_id('[tb]') is not null drop table [tb] go create table tb(ID int, PID int) insert into tb select 1,0 insert into tb select 2,1 insert into tb select 3,1 insert into tb select 4,2 insert into tb select 5,3 insert into tb select 6,5 insert into tb select 7,6 go-->函数:寻找某ID的父节点 CREATE FUNCTION f_Pid(@ID int) RETURNS @t_Level TABLE(ID int) AS BEGIN INSERT @t_Level SELECT @ID SELECT @ID=PID FROM tb WHERE ID=@ID AND PID IS NOT NULL WHILE @@ROWCOUNT>0 BEGIN INSERT @t_Level SELECT @ID SELECT @ID=PID FROM tb WHERE ID=@ID AND PID IS NOT NULL END RETURN END go-->查询 select top 3 * into #t1 from tb order by newid() declare my_cursor cursor for select id from #t1 open my_cursor declare @id int select * into #t2 from tb where 1=2 fetch next from my_cursor into @id while(@@fetch_status=0) begin insert into #t2 select t.* from tb t join dbo.f_Pid(@id) b on t.ID=b.id where not exists(select 1 from #t2 where id=t.id) fetch next from my_cursor into @id end close my_cursor deallocate my_cursor-->随机的3条记录 select * from #t1 /** ID PID ----------- ----------- 4 2 6 5 7 6(3 行受影响) **/-->随机记录所涉及到的父节点 select * from #t2 /** ID PID ----------- ----------- 1 0 2 1 4 2 3 1 5 3 6 5 7 6(7 行受影响) **/drop table #t1,t2
这个帖子不是给出函数了吗?然后嵌套一下select top 10 * from (嵌套结果) aa order by newid()
然后在根据这10条ID 读取这10条的所有节点集合... 在另一个帖子上我也说过了.用程序循环...但是数据库多的时候速度实在不能接受..一个页面打开将近10s
if object_id('[tb]') is not null drop table [tb]
go
create table tb(ID int, PID int)
insert into tb select 1,0
insert into tb select 2,1
insert into tb select 3,1
insert into tb select 4,2
insert into tb select 5,3
insert into tb select 6,5
insert into tb select 7,6
go-->函数:寻找某ID的父节点
CREATE FUNCTION f_Pid(@ID int)
RETURNS @t_Level TABLE(ID int)
AS
BEGIN
INSERT @t_Level SELECT @ID
SELECT @ID=PID FROM tb
WHERE ID=@ID
AND PID IS NOT NULL
WHILE @@ROWCOUNT>0
BEGIN
INSERT @t_Level SELECT @ID
SELECT @ID=PID FROM tb
WHERE ID=@ID
AND PID IS NOT NULL
END
RETURN
END
go-->查询
select top 3 * into #t1 from tb order by newid()
declare my_cursor cursor for select id from #t1
open my_cursor
declare @id int
select * into #t2 from tb where 1=2
fetch next from my_cursor into @id
while(@@fetch_status=0)
begin
insert into #t2
select t.* from tb t join dbo.f_Pid(@id) b on t.ID=b.id
where not exists(select 1 from #t2 where id=t.id)
fetch next from my_cursor into @id
end
close my_cursor
deallocate my_cursor-->随机的3条记录
select * from #t1
/**
ID PID
----------- -----------
4 2
6 5
7 6(3 行受影响)
**/-->随机记录所涉及到的父节点
select * from #t2
/**
ID PID
----------- -----------
1 0
2 1
4 2
3 1
5 3
6 5
7 6(7 行受影响)
**/drop table #t1,t2