sql 语句之，游标的应用，table变量

declare @tempstr nvarchar(400);   
    declare @dbTable1 table(id int,[payorderid] nvarchar(50),addtime datetime,userid nvarchar(50),my nvarchar(10),ku nvarchar(10),addpay int, shoplist139ex nvarchar(500))  //定义 table变量
    declare allyb cursor             //定义游标
      for 
    select  pay.id,pay.shoplist139ex,pay.addpay,pay.[ku],pay.my,pay.shoplist139,pay.payapli,pay.addtime,pay.userid,cast(pay.[allorderid] as nvarchar(400)) as [allorderid],pay.[payorderid],cast(paylist.shopalloid as nvarchar(400)) as shopalloid from [139group].[dbo].pay LEFT JOIN [139group].[dbo].paylist ON pay.[shoplist139ex]=paylist.pod where  pay.addtime between convert(datetime,'2014-9-21') and convert(datetime,'2014-9-22') order by pay.addtime desc,pay.id desc
    for read only
    //定义对应
     declare @id int,@shoplist139ex nvarchar(50),@addpay int,@ku nvarchar(10),@my nvarchar(10),@shoplist139 nvarchar(500); 
     declare @payapli decimal(18,2),@addtime datetime,@userid nvarchar(50),@allorderid nvarchar(400),@payorderid  nvarchar(50),@shopalloid nvarchar(400)
    open allyb    //打开游标
    fetch allyb into @id,@shoplist139ex,@addpay,@ku,@my,@shoplist139,@payapli,@addtime,@userid,@allorderid,@payorderid,@shopalloid

取得一次数据
    while @@FETCH_STATUS!=-1 begin
    set @tempstr=''
     select @tempstr+=','+orderids139 from dbo.lc_order where charindex( ','+CAST(id as nvarchar(50))+',',','+@shopalloid  )>0
         insert into @dbTable1 (id,payorderid,addtime,userid,my,ku,addpay,shoplist139ex)values(@id,@payorderid,@addtime,@userid,@my,@ku,@addpay,@tempstr)

//子查询
     fetch allyb into @id,@shoplist139ex,@addpay,@ku,@my,@shoplist139,@payapli,@addtime,@userid,@allorderid,@payorderid,@shopalloid
//再次    

end
    close allyb
    deallocate allyb
    select * from @dbTable1