SQL查询当前月每天的数据量,没有补0
需求: 查询当前月每天的数据量,此处表的名称为:ticket_ticket
直接上sql:
SELECT COUNT(*) as num, DATE(create_at) as t
FROM ticket_ticket
AND YEAR(create_at)=YEAR(CURDATE())
AND MONTH(create_at)=MONTH(CURDATE())
GROUP BY DATE(create_at)
查询结果如下:
可以看出时间不连贯,没有2021-01-03的数据。如果没有特殊要求这样的数据没有问题,但我这边是用来画折线图的,所以必须是连贯性的数据。
解决方法如下:
步骤一:生成一个日期表
sql如下:
SELECT
@cdate := date_add( @cdate, INTERVAL - 1 DAY ) as date
FROM
( SELECT @cdate := date_add(CURDATE(), INTERVAL + 1 DAY )
FROM ticket_ticket
)d1
WHERE YEAR(@cdate)=YEAR(CURDATE())
AND MONTH(@cdate)=MONTH(CURDATE())
AND DAY(@cdate) > 1
ORDER BY date
结果如下:
步骤二:将查询结果表并入日期表
sql如下:
SELECT * FROM (
SELECT
@cdate := date_add( @cdate, INTERVAL - 1 DAY ) as date
FROM
( SELECT @cdate := date_add(CURDATE(), INTERVAL + 1 DAY )
FROM ticket_ticket
)d1
WHERE YEAR(@cdate)=YEAR(CURDATE())
AND MONTH(@cdate)=MONTH(CURDATE())
AND DAY(@cdate) > 1
ORDER BY date
)date_c LEFT JOIN (
SELECT COUNT(*) as num, DATE(create_at) as t
FROM ticket_ticket WHERE flow_id=336 AND YEAR(create_at)=YEAR(CURDATE()) AND MONTH(create_at)=MONTH(CURDATE()) GROUP BY DATE(create_at)
)tab ON DATE(t)=date
结果如下:
步骤三:处理查询结果:NULL设置为0,并按照日期排序
sql如下:
SELECT date as 日期, IFNULL(tab.num, 0) as 数量 FROM (
SELECT
@cdate := date_add( @cdate, INTERVAL - 1 DAY ) as date
FROM
( SELECT @cdate := date_add(CURDATE(), INTERVAL + 1 DAY )
FROM ticket_ticket
)d1
WHERE YEAR(@cdate)=YEAR(CURDATE())
AND MONTH(@cdate)=MONTH(CURDATE())
AND DAY(@cdate) > 1
ORDER BY date
)date_c LEFT JOIN (
SELECT COUNT(*) as num, DATE(create_at) as t
FROM ticket_ticket WHERE flow_id=336 AND YEAR(create_at)=YEAR(CURDATE()) AND MONTH(create_at)=MONTH(CURDATE()) GROUP BY DATE(create_at)
)tab ON DATE(t)=date
ORDER BY date;
结果如下:
总结这里用到的sql
- ORDER BY
定义:用于对结果集按照一个列或者多个列进行排序。默认按照升序,如果需要按照降序对记录进行排序,您可以使用 DESC 关键字
用法:
SELECT column_name
FROM table_name
ORDER BY column_name ASC|DESC;
-
DATE_ADD(date,INTERVAL expr type)
定义:向指定日期添加指定的时间间隔
用法:date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔,可以为正数+3,也可以为负数-3。type是间隔的类型,可以是天DAY,也可以是MONTH,也可以是其他的。 -
DATE(date)
定义: 提取日期或日期/时间表达式的日期部分
用法:date参数可以为日期‘2020-02-19’,也可以为时间‘2020-02-19 12:20:12’,最终结果都为‘2020-02-19’ -
LEFT JOIN
定义:从左表(table1)返回所有的行,即使右表(table2)中没有匹配。如果右表中没有匹配,则结果为 NULL。
用法:
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
其中ON为合并条件,如果想直接合并,可以使用ON 1
- IFNULL(expr1,expr2)
定义:如果expr1字段为NULL,则返回值expr2