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

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