sqlserver 游标

 sqlserver 游标

declare 
@BID Int,
@GiveToUserID int,
@NickName nvarchar(100),
@StartTime datetime,
@EndTime datetime,
@PageNumber int,
@PageSize int
set @BID='999'
set @NickName=''
set @StartTime='2018-06-26'
set @EndTime='2018-09-27'
set @PageNumber=0
set @PageSize=10
        
        --创建临时表
        Create Table #responseTable(UserID int,NickName nvarchar(50),PicUrl varchar(500),
        HadPayNoRightTotalPayment decimal(18, 2), 
        HadPayHadRightTotalPayment decimal(18, 2), 
        HadPaySetingRightTotalPayment decimal(18, 2), 
        EndPercentPayTotalPayment decimal(18, 2) )
        --Create Table #tempTable1(GiveToUserID int)
        Create Table #tempTable1(rownum int,GiveToUserID int) ----这里的字段顺序一定不能反        
        --查询分页数据插入到临时表#tempTable1
        insert into #tempTable1 

        /*
        select WT.GiveToUserID 
        from WxTradePercent WT 
        left join WxUsers U on WT.GiveToUserID=U.UserID
        where WT.BID=@BID and U.NickName like '%' + @NickName + '%'  group by WT.GiveToUserID
        */

        select * from (
            select ROW_NUMBER() over(order by WT.GiveToUserID) as rownum,
            WT.GiveToUserID 
            from WxTradePercent WT 
            left join WxUsers U on WT.GiveToUserID=U.UserID
            where WT.BID=@BID and U.NickName like '%' + @NickName + '%'  group by WT.GiveToUserID
        ) as t1 
        where t1.rownum>=(@PageNumber)*@PageSize+1 and t1.rownum<=((@PageNumber+1)*@PageSize)
        --查询临时表
        --select * from #tempTable1
        --删除临时表
        --drop table #tempTable1

        --声明游标
        declare query_Cursor cursor for
        --查询临时表数据，进行游标功能，就是这个游标是针对这个临时表而游的。
        --如果这里是select * ，那么游标into的时候就要into所有字段，
        --如果是select指定的字段，那么游标into的时候就into指定得字段。
        select GiveToUserID from #tempTable1  
        --打开游标
        OPEN query_Cursor
        --获取游标的下一行数据
        FETCH NEXT FROM  query_Cursor
        into
        @GiveToUserID --使变量获得当前游标的父级ID，就是将游标里的GiveToUserID赋值给@GiveToUserID这个变量    

        --FETCH语句执行成功
        WHILE(@@FETCH_STATUS=0)

        --开始要执行的语句
        begin
        insert into #responseTable
        select * from 
                (select UserID,NickName,PicUrl from WxUsers  where BID=@BID and UserID=@GiveToUserID) as U,               
                (select COALESCE(sum(TradePayment),0) as HadPayNoRightTotalPayment from WxTradePercent where BID=@BID and GiveToUserID=@GiveToUserID and InsertTime between @StartTime and @EndTime and Status=0 ) as A,
                (select COALESCE(sum(TradePayment),0) as HadPayHadRightTotalPayment from WxTradePercent where BID=@BID and GiveToUserID=@GiveToUserID and InsertTime between @StartTime and @EndTime and Status=1 ) as B,
                (select COALESCE(sum(TradePayment),0) as HadPaySetingRightTotalPayment from WxTradePercent where BID=@BID and GiveToUserID=@GiveToUserID and InsertTime between @StartTime and @EndTime and Status=4) as C,
                (select COALESCE(sum(TradePayment),0) as EndPercentPayTotalPayment from WxTradePercent where BID=@BID and GiveToUserID=@GiveToUserID and InsertTime between @StartTime and @EndTime and Status=2) as D      

        --循环，获取游标的下一行
        FETCH NEXT FROM query_Cursor
        into
        @GiveToUserID
        --结束要执行的语句
        end 

        --关闭游标
        Close query_Cursor
        --释放游标
        Deallocate query_Cursor

        --查询返回表
        select * from #responseTable
        --释放临时表
        Drop table #tempTable1
        Drop table #responseTable

搜索

复制