记录sql中统计近五天数据的口径（While+IF）

话不多说，直接上码↓

---

IF OBJECT_ID('tempdb..#Table') IS NOT NULL
    BEGIN
        DROP TABLE #Table;
    END;

DECLARE @tbRangeDate TABLE
    (
      name NVARCHAR(10) ,
      value NVARCHAR(10)
    );
DECLARE @d INT = 7;
DECLARE @i INT = 0;
DECLARE @TotalCount INT= 5;

DECLARE @SuccessMoney NVARCHAR(100) = '';
DECLARE @PutawayMoney NVARCHAR(100) = '';
--记录星期六，星期天的金额数据
DECLARE @WeekendPutMoney DECIMAL(18, 2)= 0;
DECLARE @WeekendSuccessMoney DECIMAL(18, 2)= 0;
--DATEADD(DAY,-7,GETDATE());
DECLARE @currentDate DATETIME= GETDATE();
DECLARE @topName NVARCHAR(10) = '';
DECLARE @countValue INT= 0;
 --近五天数据

-- 声明变量
DECLARE @name AS NVARCHAR(10) ,
    @value AS NVARCHAR(10);

CREATE TABLE #Table
    (
      DisplayName VARCHAR(20) ,
      DayValue VARCHAR(20) ,
      SuccessMoney DECIMAL(18, 2) , --成交
      PutawayMoney DECIMAL(18, 2)  --上架
    );
WHILE ( @d > 0 )
    BEGIN
        SET @d = @d - 1;
        
        INSERT  INTO @tbRangeDate
        VALUES  ( CONVERT(VARCHAR(10), DATENAME(WEEKDAY,
                                                DATEADD(DAY, -( @d ),
                                                        @currentDate)), 23),
                  CONVERT(VARCHAR(10), DATEADD(DAY, -( @d ), @currentDate), 23) );
    END;


WHILE EXISTS ( SELECT   name ,
                        value
               FROM     @tbRangeDate )
    BEGIN
 -- 也可以使用top 1
        SET ROWCOUNT 1;
        SELECT  @name = name ,
                @value = value
        FROM    @tbRangeDate;
        SET @SuccessMoney = '';
        SET @PutawayMoney = '';
        SET @WeekendPutMoney = 0;
        SET @WeekendSuccessMoney = 0;

        --统计每天上架资源金额
        SELECT  @PutawayMoney = SUM(a.resourceTotalMoney)
        FROM    ( SELECT    r.Id ,
                            r.ResourceCode ,
                            ROW_NUMBER() OVER ( PARTITION BY b.ResourceId ORDER BY b.ResourceId ) AS rowNum ,
                            SUM(b.Number * b.BasePrice) resourceTotalMoney
                  FROM      ResourceManage.Resource r
                            JOIN ResourceManage.ResourceItem b ON r.Id = b.ResourceId
                  WHERE     ResourcesType IN ( 0, 1, 2, 5 )
                            AND r.IsDeleted = 0
                            AND r.IsPublish = 1
                            AND CONVERT(VARCHAR(10), r.PublishTime, 23) = @value
                  GROUP BY  r.Id ,
                            r.ResourceCode ,
                            b.ResourceId
                ) a;         
                --周末上架金额的数据
        IF @name = '星期日'
            OR @name = '星期六'
            BEGIN
                SET @WeekendPutMoney = @WeekendPutMoney
                    + CAST(@PutawayMoney AS DECIMAL(18, 2));
            END;

            
                --统计每天成交金额
        SELECT  @SuccessMoney = SUM(AmountOfMoney)
        FROM    Business.ResourceOrder
        WHERE   OrderSourceType IN ( 0, 1, 2, 5 )
                AND IsDeleted = 0
                AND CONVERT(VARCHAR(10), CreationTime, 23) = @value;
        --周末成交金额的数据
        IF @name = '星期日'
            OR @name = '星期六'
            BEGIN
                SET @WeekendSuccessMoney = @WeekendSuccessMoney
                    + CAST(@SuccessMoney AS DECIMAL(18, 2));
            END;


        INSERT  INTO #Table
                ( DisplayName ,
                  DayValue ,
                  SuccessMoney ,
                  PutawayMoney
                )
        VALUES  ( @name ,
                  @value ,
                  @SuccessMoney ,
                  @PutawayMoney
                );

        SET ROWCOUNT 0;
        DELETE  FROM @tbRangeDate
        WHERE   name = @name;
    END;

SELECT TOP 1
        @topName = DisplayName
FROM    #Table;

SELECT  @countValue = COUNT(1)
FROM    #Table
WHERE   DisplayName = '星期六'
        OR DisplayName = '星期日';

WHILE ( @i = 0 )
    BEGIN
        SET @i = @i + 1;
        DELETE  FROM #Table
        WHERE   DisplayName = '星期六'
                OR DisplayName = '星期日';
        IF @countValue = 2
            AND ( @topName = '星期二'
                  OR @topName = '星期三'
                  OR @topName = '星期四'
                  OR @topName = '星期五'
                )
            BEGIN
                UPDATE  #Table
                SET     PutawayMoney = PutawayMoney + @WeekendPutMoney
                WHERE   DisplayName = '星期一';
            END;
    END;


SELECT  *
FROM    #Table;    

IF OBJECT_ID('tempdb..#Table') IS NOT NULL
    BEGIN
        DROP TABLE #Table;
    END;