1. 问题复现:
按照天数统计每天的总数, 如果其中有几天没有数据, 那么 group by 返回会忽略那几天, 如何填充 0? 如下图, 统计的 10-3~10-10 7 天的数据, 其中只有 8 号和 10 号有数据, 这样返回, 数据只有 2 个, 不符合报表统计的需求期望没有值填 0
2. 换个思维:
我们用一组连续的天数作为左表然后 left join 要查询的数据 最后 group by.: 连续天数表 t1 left join 业务数据 t2 group by t1.day , 如下图:
- SELECT
- t1.`day`,
- COUNT(t2.user_id) payment_num
- FROM
- (SELECT
- @cdate := DATE_ADD(@cdate, INTERVAL - 1 DAY) DAY
- FROM
- (SELECT
- @cdate := DATE_ADD('20171219', INTERVAL + 1 DAY)
- FROM
- order) t0
- LIMIT 7) t1
- LEFT JOIN
- (SELECT
- DATE(a.create_time) DAY,
- a.user_id
- FROM
- pay_payment a
- JOIN dealer_store b
- ON a.order_no = b.order_no
- WHERE DATE(create_time) <= '20171219'
- AND DATE(create_time) > DATE_SUB('20171219', INTERVAL 7 DAY)
- ) t2
- ON t2.day = t1.day
- GROUP BY t1.`day`;
2.1 连续天数表
- SELECT
- @cdate := DATE_ADD(@cdate, INTERVAL - 1 DAY) DAY
- FROM
- (SELECT
- @cdate := DATE_ADD('20171219', INTERVAL + 1 DAY)
- FROM
- order) t0
- LIMIT 7
执行结果如下:
SQL 分析:
1.
@cdate: = 是定义名为 cdate 的变量并赋值 (select 后面必须用: = )
- 2
- .@cdate: =DATE_ADD('20171219', INTERVAL + 1 DAY)
按照传入的日期'20171219', 加一天
- 3.
- SELECT@cdate: =DATE_ADD('20171219', INTERVAL + 1 DAY) FROM`order`
找一张表记录肯定大于 10 条的即可, 执行结果如下:
- 4
- .@cdate: =DATE_ADD(@cdate, INTERVAL - 1 DAY) DAY
把定义的 cdate 变量天数 - 1(自减)
5.LIMIT 7 限制一下条数, 大功告成, 我们得到了指定日期往前 7 天的记录
2.2 左关联然后分组
left join group by t1.day
即按照左表关联业务数据, 根据左表的日期分组, 即分成了指定的 7 天数据, 有记录就统计条数, 没有记录就是 0
最终执行结果:
来源: http://www.phperz.com/article/18/0214/363118.html