[难题],高分求一SQL递归.高手请来挑战............

[popeyepower]

表结构.
ID parentid typeid
1 0 4
2 1 4
3 2 4
4 1 4

递归的要求:
1. 能传入PAGESIZE,PAGEINDEX,TYPEID 分页按照ID倒序
2. 根据PAGESIZE,PAGEINDEX,TYPEID取出记录集A 依次插入临时表.
3. 再用递归取出记录集A中所有的父结点的记录,追加(一定要是追加)到临时表中.
4. 最好能用SQLSERVER2005 ROWNUMBER分页的方式.

不好意思.T-SQL实在是不好又急着要用.麻烦各位大虾.200分送上.

[mywisest]

CREATE TABLE #origin(id varchar(4) NOT NULL,parentid varchar(4) NULL,typeid varchar(4) NULL) ON [PRIMARY]
ALTER TABLE #origin ADD CONSTRAINT	PK_Table1 PRIMARY KEY CLUSTERED (id)
Insert into #origin values(1,0,4);
Insert into #origin values(2,1,4);
Insert into #origin values(3,2,4);
Insert into #origin values(4,1,4);
Insert into #origin values(5,3,4);
Insert into #origin values(6,5,2);

declare @pageSize int;
declare @pageIndex int;
declare @typeid varchar(10);
declare @rows int;
declare @parentid varchar(10);

--设置初始变量
set @pageSize = 1;
set @pageIndex = 3;
set @typeid = 4;
--创建临时表
create table #tmp (id varchar(10),parentid varchar(10));
--插入记录数
WITH OrderedTbl AS (select id,parentid,row_number() OVER(order BY id) AS 'ROWSID' from #origin where typeid=@typeid) 
insert into #tmp SELECT id,parentid FROM OrderedTbl WHERE ROWSID BETWEEN @pageSize * (@pageIndex -1) and @pageSize * @pageIndex
--寻找父节点数据
declare findParent cursor for select parentid from (select distinct parentid from #tmp ) a where parentid not in (select distinct id from #tmp)
open findParent
fetch next from findParent into @parentid
while @@fetch_status =0
begin
	insert into #tmp select id,parentid from #origin where id=@parentid
	fetch next from findParent into @parentid
end
close findParent;
DEALLOCATE findParent
--显示所有数据
select * from #tmp order by id,parentid
--清理现场
drop table #tmp
drop table #origin

输出值为：
1 0
2 1
3 2

[mywisest]

结贴吧，嘿嘿。

[popeyepower]

结了,,,多谢.
wisest是游标的方法.
jinjazz是循环的方法.
不知道哪种效率高点呢?

[shoushii]

学习中……

[jinjazz]

效率哪个高，海量环境下测试一下就知道了

[mywisest]

效率应该是Cursor稍微好一些，消耗的资源相对很少
后面半句是MSDN中说的，嘿嘿。

[suiqirui19872005]

顶............

[烈火焚身]

UP

[fellowcheng]

create   table   t_1(code   varchar(10),parent   varchar(10)) 
insert   into   t_1(code,parent) 
select   'A ', '0 ' 
union   all   select   'E ', 'A ' 
union   all   select   'E01 ', 'E ' 
union   all   select   'E02 ', 'E ' 

create   table   t_2(code   varchar(10),value   int) 
insert   into   t_2(code,value) 
select   'E01 ',3 
union   all   select   'E02 ',4 

--递归取下级 
create   function   f_getchild(@code   varchar(10)) 
returns   @t   table(code   varchar(10)) 
as 
begin 
declare   @t_temp   table(id   int   identity(1,1),child   varchar(10)) 
insert   into   @t(code) 
select   code   from   t_1   where   Parent   =   @code 

insert   into   @t_temp(child) 
select   code   from   t_1   where   Parent   =   @code 

declare   @child_temp     varchar(10),@max_id     int,@min_id   int 
select   @max_id   =   max(id),@min_id   =   min(id)   from   @t_temp 
while   @min_id   <=   @max_id 
begin 
select   @child_temp   =   child   from   @t_temp   where   id   =   @min_id 
insert   into   @t(code) 
select   *   from   dbo.f_getchild(@child_temp) 
select   @min_id   =   @min_id   +   1 
end 
return 
end 
--调用 
select   code   from   dbo.f_getchild( 'E01 ') 

[popeyepower]

3楼朋友.
select附近语法错误.

[amandag]

可发到SQL版

[popeyepower]

额....
哪那么多分来烧呢.....那边人丁不旺哦........

[mywisest]

等上班后我来整整。

[正宗熊猫哥]

人气就这里好。
UP，学习

[Flow_Cloud]

哪儿人多就在哪儿发，要不没人看

[xierfly]

[Quote=引用 6 楼 popeyepower 的回复:]
额....
哪那么多分来烧呢.....那边人丁不旺哦........
[/Quote]

看来还是这块热闹啊.

[jinjazz]

如果父id对应的纪录已经插入，后面一步还需要追加吗？

[wxsan]

mark

[超级大笨狼]

http://www.dullwolf.cn/Chess/

和我下盘棋

[SlaughtChen]

顶一个先