SQL-按日期不间断统计

一、数据库 日期数据不间断

             SELECT COUNT(1) AS count, SUM(age) AS age FROM `user`

             GROUP BY DATE_FORMAT(create_time,'%Y-%m-%d');

 

二、数据库 日期数据不连续 间断补0

             1.不创建表方式 （临时表 -- 只读库也可以创建）

             临时表 只会存在一个查询中，如果另开一个查询则会

             sql error： table doesn't exist。

 

             CREATE TEMPORARY TABLE num (i int); -- 创建临时表

             INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9); -- 插入增加的天数

             select adddate('2012-09-01', num.i) as `date` from num 

             where adddate('2012-09-01', num.i) <= '2012-09-10'; -- 查询连续天数

             '2012-09-01' – 开始时间

             '2012-09-10' – 结束时间

             查询结果：

             2012-09-01

             2012-09-02

             2012-09-03

             2012-09-04

             2012-09-05

             2012-09-06

             2012-09-07

             2012-09-08

             2012-09-09

             2012-09-10

            备注： 如果需要增加连续的天数，则增加 num  改变查询的 开始 结束时间

            例： 增加num 0 - 10000；开始时间为 2010-01-01  结束时间为2018-12-31；

            1.创建表方式 （临时表 -- 只读库也可以创建）

CREATE TABLE num (i int);
INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
CREATE TABLE  if not exists calendar(datelist date); 

INSERT INTO calendar (datelist) SELECT adddate( ( DATE_FORMAT("2018-01-01", '%Y-%m-%d') ), numlist.id ) AS `date` FROM ( 
SELECT n1.i + n10.i * 10 + n100.i * 100 + n1000.i * 1000 + n10000.i * 10000 AS id
FROM num n1 CROSS JOIN num AS n10 CROSS JOIN num AS n100 CROSS JOIN num AS n1000 CROSS JOIN num AS n10000)AS numlist;

drop table num;

            "2018-01-01" --开始时间 

            增加10000天。

天数连续的表有了， 在统计的时候就可以连续统计出来了。

 

 

 

另外： SUM（）为空时，转为0

                      COALESCE(SUM(pay_amount),0)

保留两位小数：

                      ROUND(COALESCE(SUM(epc.pay_amount),0), 2)