MSSQL中循环表
在很多ERP中进行表循环，是非常简单的。如QAD中用For each table就可以循环表，AX中用While select table。
在MSSQL中，可以通过两种方式进行表循环。
1、通过游标。
1 declare @result table 2 ( 3 custid int, 4 ordermonth datetime, 5 qty int, 6 runqty int, 7 primary key(custid,ordermonth) 8 ); 9 10 declare 11 @custid as int, 12 @prvcustid as int, 13 @ordermonth as datetime, 14 @qty as int, 15 @runqty as int; 16 declare c cursor fast_forward for --定义游标 17 select custid,ordermonth,qty 18 from Sales.CustOrders 19 order by custid,ordermonth; 20 21 open c --打开游标 22 fetch next from c into @custid,@ordermonth,@qty; 23 select @prvcustid = @custid,@runqty = 0; 24 while @@fetch_status = 0 --当不是最后一行记录时，函数返回0 25 begin 26 --Do something 27 end 28 close c; --关闭游标 29 deallocate c; --释放游标
2、通过特定ID。
1 declare @max int 2 declare @i int 3 select ROW_NUMBER() over (order by UnitId) as 'Id',RECID into #temp from UNIT 4 select @max=max(Id) from #temp 5 set @i = 1 6 while (@i <= @max) 7 begin 8 if @i = 10 9 begin 10 select * from UNIT where RECID = (select RECID from #temp where ID = @i) --11125166 11 break 12 end 13 set @i = @i + 1 14 end 15 16 drop table #temp
