mysql查询今天每小时,本周、本月每天,本年每月的数据统计方法记录,没有数据补0

查询一天24小时的或者查询一个星期7天或者一年12个月,首先要画好格式,格式sql如下

SELECT
                    DATE_FORMAT(
                    @cdate := DATE_ADD(@cdate, INTERVAL - 1 HOUR),
                    '%y-%m-%d %H'
                 ) HOUR
              FROM
                 (
                    SELECT
                       @cdate := DATE_ADD(
                          DATE_FORMAT('起始时间', '%y-%m-%d %H'),
                          INTERVAL + 1 HOUR
                       )
                    FROM
                       表(任意一张超过24条数据的表)
                 ) t0
              LIMIT 24

下面是我查询营业额的sql

SELECT
          '销售额' item,
          DATE_FORMAT(t1.HOUR, '%H') as keykey,
          ifNull(SUM(t2.payment)/100,0) value
        FROM
           (
              SELECT
                 DATE_FORMAT(
                    @cdate := DATE_ADD(@cdate, INTERVAL - 1 HOUR),
                    '%y-%m-%d %H'
                 ) HOUR
              FROM
                 (
                    SELECT
                       @cdate := DATE_ADD(
                          DATE_FORMAT('2019-10-21 23:59:59'), '%y-%m-%d %H'),
                          INTERVAL + 1 HOUR
                       )
                    FROM
                       basic_log
                 ) t0
              LIMIT 24
           ) t1
        LEFT JOIN (
           SELECT
              DATE_FORMAT(add_data_time, '%y-%m-%d %H') HOUR,
              payment
           FROM
              t_order
           WHERE 1 = 1 AND order_type = 'consume' AND order_source IN ('drinks', 'compote')
        ) t2 ON t1. HOUR = t2. HOUR
        GROUP BY
           t1. HOUR
        ORDER BY
           t1. HOUR

查询一周7天的时候,要将模板换一下

SELECT
          '销售额' item,
          DATE_FORMAT(t1.HOUR, '%d') as keykey,
          ifNull(SUM(t2.payment)/100,0) value
        FROM
           (
              SELECT
                 DATE_FORMAT(
                    @cdate := DATE_ADD(@cdate, INTERVAL - 1 DAY),
                    '%y-%m-%d'
                 ) HOUR
              FROM
                 (
                    SELECT
                       @cdate := DATE_ADD(
                          DATE_FORMAT('2019-10-21', '%y-%m-%d'),
                          INTERVAL + 1 DAY
                       )
                    FROM
                       basic_log
                 ) t0
              LIMIT 7
           ) t1
        LEFT JOIN (
           SELECT
              DATE_FORMAT(add_data_time, '%y-%m-%d') HOUR,
              payment
           FROM
              t_order
           WHERE 1 = 1 AND order_type = 'consume' AND order_source IN ('drinks', 'compote')
        ) t2 ON t1. HOUR = t2. HOUR
        GROUP BY
           t1. HOUR
        ORDER BY
           t1. HOUR

查询一年12个月

SELECT
          '销售额' item,
          DATE_FORMAT(t1.HOUR, '%m') as keykey,
          ifNull(SUM(t2.payment)/100,0) value
        FROM
           (
              SELECT
                 DATE_FORMAT(
                    @cdate := DATE_ADD(@cdate, INTERVAL - 1 MONTH),
                    '%y-%m-%d'
                 ) HOUR
              FROM
                 (
                    SELECT
                       @cdate := DATE_ADD(
                          DATE_FORMAT('2019-12-31', '%y-%m-%d'),
                          INTERVAL + 1 MONTH
                       )
                    FROM
                       basic_log
                 ) t0
              LIMIT 12
           ) t1
        LEFT JOIN (
           SELECT
              DATE_FORMAT(add_data_time, '%y-%m') HOUR,
              payment
           FROM
              t_order
        ) t2 ON DATE_FORMAT(t1.HOUR, '%y-%m') = t2. HOUR
        GROUP BY
           t1. HOUR
        ORDER BY
           t1. HOUR

如果对您有帮助,麻烦点个赞,如果有问题欢迎指出

  • 2
    点赞
  • 4
    收藏
    觉得还不错? 一键收藏
  • 2
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值