mysql查询本周每日的数据量及金额

wyf0518

已于 2024-09-14 14:31:08 修改

阅读量245

点赞数 2

文章标签： mysql

于 2024-09-14 14:30:09 首次发布

本文链接：https://blog.csdn.net/wyf0518/article/details/142257367

版权

查询当前日期函数 DATE(now())

查询本周的周一的日期：DATE_SUB(commitdate, INTERVAL DAYOFWEEK(commitdate) - 2 DAY)

就可以将周一的日期和日期字段进行比较，相等就代表是本周的周一，使用sum进行统计即可

需求是查询本周每日数据变化，简化一下是下面这个样子，需要的话根据具体需求进行调整

select `week`, count(id) num, IFNULL(CONVERT(sum(ifnull(amount,0)),DECIMAL(20,2)),0) totalamt from(
    select o.id, o.amount, o.commitdate,
    case
        when date(o.commitdate)=date(DATE_SUB(o.commitdate, INTERVAL DAYOFWEEK(o.commitdate) - 2 DAY)) then '周一'
        when date(o.commitdate)=date(DATE_SUB(o.commitdate, INTERVAL DAYOFWEEK(o.commitdate) - 3 DAY)) then '周二'
        when date(o.commitdate)=date(DATE_SUB(o.commitdate, INTERVAL DAYOFWEEK(o.commitdate) - 4 DAY)) then '周三'
        when date(o.commitdate)=date(DATE_SUB(o.commitdate, INTERVAL DAYOFWEEK(o.commitdate) - 5 DAY)) then '周四'
        when date(o.commitdate)=date(DATE_SUB(o.commitdate, INTERVAL DAYOFWEEK(o.commitdate) - 6 DAY)) then '周五'
        when date(o.commitdate)=date(DATE_SUB(o.commitdate, INTERVAL DAYOFWEEK(o.commitdate) - 7 DAY)) then '周六'
        when date(o.commitdate)=date(DATE_SUB(o.commitdate, INTERVAL DAYOFWEEK(o.commitdate) - 1 DAY)) then '周日'
        end as `week`
    from product_orders o
    where o.year = '2024'
    and o.commitdate >= DATE_SUB(CURDATE(), INTERVAL DAYOFWEEK(CURDATE()) - 2 DAY) 
    and o.commitdate < DATE_SUB(CURDATE(), INTERVAL DAYOFWEEK(CURDATE()) - 8 DAY)
)t
group by `week`

第二种，列转行，这种只能统计数量或者金额

select 
sum(if(date(commitdate)=date(DATE_SUB(commitdate, INTERVAL DAYOFWEEK(commitdate) - 2 DAY)),1,0)) '周一',
sum(if(date(commitdate)=date(DATE_SUB(commitdate, INTERVAL DAYOFWEEK(commitdate) - 3 DAY)),1,0)) '周二',
sum(if(date(commitdate)=date(DATE_SUB(commitdate, INTERVAL DAYOFWEEK(commitdate) - 4 DAY)),1,0)) '周三',
sum(if(date(commitdate)=date(DATE_SUB(commitdate, INTERVAL DAYOFWEEK(commitdate) - 5 DAY)),1,0)) '周四',
sum(if(date(commitdate)=date(DATE_SUB(commitdate, INTERVAL DAYOFWEEK(commitdate) - 6 DAY)),1,0)) '周五',
sum(if(date(commitdate)=date(DATE_SUB(commitdate, INTERVAL DAYOFWEEK(commitdate) - 7 DAY)),1,0)) '周六',
sum(if(date(commitdate)=date(DATE_SUB(commitdate, INTERVAL DAYOFWEEK(commitdate) - 1 DAY)),1,0)) '周日'
from product_orders
where commitdate >= DATE_SUB(CURDATE(), INTERVAL DAYOFWEEK(CURDATE()) - 2 DAY) 
and commitdate < DATE_SUB(CURDATE(), INTERVAL DAYOFWEEK(CURDATE()) - 8 DAY)