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

话不多说，直接上码↓

 

 

  1 IF OBJECT_ID('tempdb..#Table') IS NOT NULL
  2     BEGIN
  3         DROP TABLE #Table;
  4     END;
  5 
  6 DECLARE @tbRangeDate TABLE
  7     (
  8       name NVARCHAR(10) ,
  9       value NVARCHAR(10)
 10     );
 11 DECLARE @d INT = 7;
 12 DECLARE @i INT = 0;
 13 DECLARE @TotalCount INT= 5;
 14 
 15 DECLARE @SuccessMoney NVARCHAR(100) = '';
 16 DECLARE @PutawayMoney NVARCHAR(100) = '';
 17 --记录星期六，星期天的金额数据
 18 DECLARE @WeekendPutMoney DECIMAL(18, 2)= 0;
 19 DECLARE @WeekendSuccessMoney DECIMAL(18, 2)= 0;
 20 --DATEADD(DAY,-7,GETDATE());
 21 DECLARE @currentDate DATETIME= GETDATE();
 22 DECLARE @topName NVARCHAR(10) = '';
 23 DECLARE @countValue INT= 0;
 24  --近五天数据
 25 
 26 -- 声明变量
 27 DECLARE @name AS NVARCHAR(10) ,
 28     @value AS NVARCHAR(10);
 29 
 30 CREATE TABLE #Table
 31     (
 32       DisplayName VARCHAR(20) ,
 33       DayValue VARCHAR(20) ,
 34       SuccessMoney DECIMAL(18, 2) , --成交
 35       PutawayMoney DECIMAL(18, 2)  --上架
 36     );
 37 WHILE ( @d > 0 )
 38     BEGIN
 39         SET @d = @d - 1;
 40         
 41         INSERT  INTO @tbRangeDate
 42         VALUES  ( CONVERT(VARCHAR(10), DATENAME(WEEKDAY,
 43                                                 DATEADD(DAY, -( @d ),
 44                                                         @currentDate)), 23),
 45                   CONVERT(VARCHAR(10), DATEADD(DAY, -( @d ), @currentDate), 23) );
 46     END;
 47 
 48 
 49 WHILE EXISTS ( SELECT   name ,
 50                         value
 51                FROM     @tbRangeDate )
 52     BEGIN
 53  -- 也可以使用top 1
 54         SET ROWCOUNT 1;
 55         SELECT  @name = name ,
 56                 @value = value
 57         FROM    @tbRangeDate;
 58         SET @SuccessMoney = '';
 59         SET @PutawayMoney = '';
 60         SET @WeekendPutMoney = 0;
 61         SET @WeekendSuccessMoney = 0;
 62 
 63         --统计每天上架资源金额
 64         SELECT  @PutawayMoney = SUM(a.resourceTotalMoney)
 65         FROM    ( SELECT    r.Id ,
 66                             r.ResourceCode ,
 67                             ROW_NUMBER() OVER ( PARTITION BY b.ResourceId ORDER BY b.ResourceId ) AS rowNum ,
 68                             SUM(b.Number * b.BasePrice) resourceTotalMoney
 69                   FROM      ResourceManage.Resource r
 70                             JOIN ResourceManage.ResourceItem b ON r.Id = b.ResourceId
 71                   WHERE     ResourcesType IN ( 0, 1, 2, 5 )
 72                             AND r.IsDeleted = 0
 73                             AND r.IsPublish = 1
 74                             AND CONVERT(VARCHAR(10), r.PublishTime, 23) = @value
 75                   GROUP BY  r.Id ,
 76                             r.ResourceCode ,
 77                             b.ResourceId
 78                 ) a;         
 79                 --周末上架金额的数据
 80         IF @name = '星期日'
 81             OR @name = '星期六'
 82             BEGIN
 83                 SET @WeekendPutMoney = @WeekendPutMoney
 84                     + CAST(@PutawayMoney AS DECIMAL(18, 2));
 85             END;
 86 
 87             
 88                 --统计每天成交金额
 89         SELECT  @SuccessMoney = SUM(AmountOfMoney)
 90         FROM    Business.ResourceOrder
 91         WHERE   OrderSourceType IN ( 0, 1, 2, 5 )
 92                 AND IsDeleted = 0
 93                 AND CONVERT(VARCHAR(10), CreationTime, 23) = @value;
 94         --周末成交金额的数据
 95         IF @name = '星期日'
 96             OR @name = '星期六'
 97             BEGIN
 98                 SET @WeekendSuccessMoney = @WeekendSuccessMoney
 99                     + CAST(@SuccessMoney AS DECIMAL(18, 2));
100             END;
101 
102 
103         INSERT  INTO #Table
104                 ( DisplayName ,
105                   DayValue ,
106                   SuccessMoney ,
107                   PutawayMoney
108                 )
109         VALUES  ( @name ,
110                   @value ,
111                   @SuccessMoney ,
112                   @PutawayMoney
113                 );
114 
115         SET ROWCOUNT 0;
116         DELETE  FROM @tbRangeDate
117         WHERE   name = @name;
118     END;
119 
120 SELECT TOP 1
121         @topName = DisplayName
122 FROM    #Table;
123 
124 SELECT  @countValue = COUNT(1)
125 FROM    #Table
126 WHERE   DisplayName = '星期六'
127         OR DisplayName = '星期日';
128 
129 WHILE ( @i = 0 )
130     BEGIN
131         SET @i = @i + 1;
132         DELETE  FROM #Table
133         WHERE   DisplayName = '星期六'
134                 OR DisplayName = '星期日';
135         IF @countValue = 2
136             AND ( @topName = '星期二'
137                   OR @topName = '星期三'
138                   OR @topName = '星期四'
139                   OR @topName = '星期五'
140                 )
141             BEGIN
142                 UPDATE  #Table
143                 SET     PutawayMoney = PutawayMoney + @WeekendPutMoney
144                 WHERE   DisplayName = '星期一';
145             END;
146     END;
147 
148 
149 SELECT  *
150 FROM    #Table;    
151 
152 IF OBJECT_ID('tempdb..#Table') IS NOT NULL
153     BEGIN
154         DROP TABLE #Table;
155     END;

 

posted @ 2019-08-20 16:12  麋鹿星空  阅读(522)  评论(0)  编辑  收藏  举报