有勇气的牛排博客
☰

MySQL 根据日期查询并统计数据

有勇气的牛排 1116 数据库 2023-05-18 21:54:45
知止自律打卡-微信小程序APP
一款微信自律打卡小程序，支持自定义打卡项目、习惯记录、项目日记、专注计时与数据统计。自律打卡、学习打卡、读书打卡、健身打卡、运动打卡、减肥打卡、戒色打卡、养生打卡、戒烟、戒酒、戒熬夜等多种场景。万能打卡模板自由创建。可自定义每日打卡目标，记录习惯连续天数，可视化追踪成长进度。
了解产品详情 →
微信搜索「知止自律打卡」
知止自律打卡微信小程序

文章目录

1 查询前7天的日期

select a.click_date
from (
    SELECT date_sub(curdate(), interval 1 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 2 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 3 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 4 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 5 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 6 day) as click_date
		union all
    SELECT date_sub(curdate(), interval 7 day) as click_date
) a

查询结果

image.png

2 查询订单表出现日期，成交额总和

没有出现的日期，不会被查出来

select DATE_FORMAT(o_id,"%Y-%m-%d") as date,sum(o_turnover)*0.15 u_commission from orders 
where o_id between "2021-05-26" and "2021-06-02" and uid = 2 
group by date;

image.png

3 根据日期统计数据

select DATE_FORMAT(date,"%Y-%m-%d") day,count(*) num from log group by day;

image.png

参考：
https://blog.csdn.net/weixin_38414968/article/details/108164479

评论区

×
×