SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:      <xxxx>-- Create date: <2011-03-15>-- Description: <道具系统日报表统计>-- =============================================ALTER PROCEDURE [dbo].[sp_PropsReport_DaySync]ASBEGIN--循环读取TbApps里的数据--声明游标DECLARE cursor_App CURSORFORSELECT Id FROM TbApps--打开游标OPEN cursor_AppDECLARE @AppId intDECLARE @ReportDay VARCHAR(10), @OrderCount INT, @ActAmt INT,@PersonCount INTSELECT @ReportDay=CONVERT(VARCHAR(10),DATEADD(dd, -1, GETDATE()),120)--统计TbPropsOrder里的Status=2的所有已扣费数据FETCH NEXT FROM cursor_App INTO @AppIdWHILE @@fetch_status=0BEGIN    SET @ActAmt=0    SET @OrderCount=0    SET @PersonCount=0        SELECT @ActAmt=ISNULL(SUM(ActPay),0), @OrderCount=COUNT(1),@PersonCount=COUNT(DISTINCT SndaId)    FROM TbPropsOrder(NOLOCK)    WHERE AppId=@AppId AND Status=2     AND LogTime>=@ReportDay AND LogTime<CONVERT(VARCHAR(10),GETDATE(),120)        IF EXISTS(SELECT 1 FROM TbPropsOrderDayReport(NOLOCK) WHERE ReportDay=@ReportDay AND AppId=@AppId)        UPDATE TbPropsOrderDayReport        SET OrderCount=@OrderCount, ActAmt=@ActAmt ,PersonCount = @PersonCount        WHERE ReportDay=@ReportDay AND AppId=@AppId    ELSE        INSERT TbPropsOrderDayReport(ReportDay, OrderCount, ActAmt, AppId,PersonCount)        VALUES(@ReportDay, @OrderCount, @ActAmt, @AppId,@PersonCount)            FETCH NEXT FROM cursor_App INTO @AppIdEND--关闭游标CLOSE cursor_App--释放游标DEALLOCATE cursor_AppENDGOSET ANSI_NULLS OFFGOSET QUOTED_IDENTIFIER OFFGO