;with cte 当第二次循环的时候怎样清楚Cte 之前的数据

usernamezero 2010-06-26 12:06:40

begin

declare @JiaGeLaiYuan table( CaiLiaoJieGe varchar(1000),Orders2ID varchar(50))
--声明一个用来循环的表 @whileTable 并插入数据用Orders2ID循环
declare @whileTable table( whiletableOrders2 varchar(50))
insert into @whileTable(whiletableOrders2)
select ID from Orders2 O2 where O2.OrdersID='20100530101615703644FCD9C5CCDC' and O2.DeleteTag='0'
--声明一个变量来存放循环的Orders2ID
declare @whileOrders2ID varchar(50);
--声明一个临时表存放所用价格来源数据
--declare @t1 table (CaiLiao varchar(100),JiaGe decimal(18,2),Orders2ID varchar(50),ProductNo varchar(50))
--声明一个游标来循环
declare whileCursor cursor for select whiletableOrders2 from @whileTable;
open whileCursor
fetch next from whileCursor into @whileOrders2ID
while @@fetch_status=0
begin
declare @t1 table (CaiLiao varchar(100),JiaGe decimal(18,2),Orders2ID varchar(50),ProductNo varchar(50))
declare @intt int
delete @t1
insert into @t1(CaiLiao,JiaGe,Orders2ID,ProductNo)
select (case P.OtherID when '1' then '钢芯' when '2' then '穿轴'
when '3' then '轴承' when '4' then '胶料' when '5' then '附件' end ) CaiLiao,
MarketPrice,Orders2ID,ProductNo from Orders2MarketPrice P
where Orders2ID=@whileOrders2ID order by Orders2ID,OtherID
--select * from @t1
set @intt=(select count(1) from @t1 );
--select @intt
declare @whileint int
set @whileint=0;
declare @t table ( k int identity, a varchar(500),Orders2ID varchar(50) )
insert into @t(a,Orders2ID)
select (CaiLiao+' :'+Cast(JiaGe as varchar(50))) CaiLiaoJiaGe,Orders2ID from @t1;
while 1=1
begin
;with cte(a,k,Orders2ID)
as
( select cast(t.a as varchar(max)), t.k ,t.Orders2ID from @t t where k = 1
union all
select c.a + cast(t.a as varchar(max)), t.k,t.Orders2ID from @t t
join cte c on t.k = c.k+1 )
select * from Cte
-- insert into @JiaGeLaiYuan(CaiLiaoJieGe,Orders2ID)
-- select top 1 a,Orders2ID from cte order by k desc
break
-- set @whileint=(@whileint+1)
-- print @whileint
-- if @whileint>@intt
-- begin
-- break;
-- end
end
fetch next from whileCursor into @whileOrders2ID
end
close whileCursor;
deallocate whileCursor;
select * from @JiaGeLaiYuan
end

如题

...全文