可做报表数据用的SQL

以你之姓@ 2024-04-22 06:33 37阅读 0赞

### 做日统计报表 ###

#近一周
    SELECT (@s:=@s+1) AS
                _index,STR_TO_DATE(DATE(DATE_SUB(CURRENT_DATE,INTERVAL @s
                DAY)), "%Y-%m-%d") AS _date
                FROM information_schema.CHARACTER_SETS,(SELECT @s:=-1) AS init WHERE
                @s < 6

#### 查询得到的数据 ####

<table> 
 <thead> 
  <tr> 
   <th align="left">_index</th> 
   <th align="left">_date</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">0</td> 
   <td align="left">2021-09-17</td> 
  </tr> 
  <tr> 
   <td align="left">1</td> 
   <td align="left">2021-09-16</td> 
  </tr> 
  <tr> 
   <td align="left">2</td> 
   <td align="left">2021-09-15</td> 
  </tr> 
  <tr> 
   <td align="left">3</td> 
   <td align="left">2021-09-14</td> 
  </tr> 
  <tr> 
   <td align="left">4</td> 
   <td align="left">2021-09-13</td> 
  </tr> 
  <tr> 
   <td align="left">5</td> 
   <td align="left">2021-09-12</td> 
  </tr> 
  <tr> 
   <td align="left">6</td> 
   <td align="left">2021-09-11</td> 
  </tr> 
 </tbody> 
</table>

### 做周统计 ###

#近一月中每个月的日期
    SELECT (@s:=@s+1) AS
    				_index,WEEK(DATE(DATE_SUB(CURRENT_DATE,INTERVAL @s WEEK))) AS _date
    				FROM information_schema.CHARACTER_SETS,(SELECT @s:=-2) AS init
    				WHERE @s < 2
    				ORDER BY _date
    				
    #可用于周统计的函数（1表示把星期一当做一周的第一天）
     week("需要联合表的字段",1) as timeStr

#### 查询得到的数据（\_data为今年的第几周） ####

<table> 
 <thead> 
  <tr> 
   <th align="left">_index</th> 
   <th align="left">_date</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">2</td> 
   <td align="left">35</td> 
  </tr> 
  <tr> 
   <td align="left">1</td> 
   <td align="left">36</td> 
  </tr> 
  <tr> 
   <td align="left">0</td> 
   <td align="left">37</td> 
  </tr> 
  <tr> 
   <td align="left">-1</td> 
   <td align="left">38</td> 
  </tr> 
 </tbody> 
</table>

### 做月统计 ###

#近半年
    SELECT (@s:=@s+1) AS
                _index,DATE_FORMAT(DATE_SUB(CURRENT_DATE,INTERVAL @s
                MONTH),"%Y-%m") AS _date
                FROM information_schema.CHARACTER_SETS,(SELECT @s:=-1) AS init
                WHERE @s < 5 ORDER BY _date

#### 查询到的结果 ####

<table> 
 <thead> 
  <tr> 
   <th align="left">_index</th> 
   <th align="left">_date</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">5</td> 
   <td align="left">2021-04</td> 
  </tr> 
  <tr> 
   <td align="left">4</td> 
   <td align="left">2021-05</td> 
  </tr> 
  <tr> 
   <td align="left">3</td> 
   <td align="left">2021-06</td> 
  </tr> 
  <tr> 
   <td align="left">2</td> 
   <td align="left">2021-07</td> 
  </tr> 
  <tr> 
   <td align="left">1</td> 
   <td align="left">2021-08</td> 
  </tr> 
  <tr> 
   <td align="left">0</td> 
   <td align="left">2021-09</td> 
  </tr> 
 </tbody> 
</table>