做日统计报表
#近一周 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
查询得到的数据
_index |
_date |
0 |
2021-09-17 |
1 |
2021-09-16 |
2 |
2021-09-15 |
3 |
2021-09-14 |
4 |
2021-09-13 |
5 |
2021-09-12 |
6 |
2021-09-11 |
做周统计
#近一月中每个月的日期 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为今年的第几周）
| _index | _date |
2 |
35 |
1 |
36 |
0 |
37 |
-1 |
38 |
做月统计
#近半年 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
查询到的结果
_index |
_date |
5 |
2021-04 |
4 |
2021-05 |
3 |
2021-06 |
2 |
2021-07 |
1 |
2021-08 |
0 |
2021-09 |
