西瓜の

-人类是因为记录才进入文明-

2019/05/5
19:25
technology

mysql日期统计查询

格式化日期字段为年月日格式:

DATE_FORMAT(create_time, ‘%Y%m%d’)

通过日期查询对应的日期的总数:

SELECT
DATE_FORMAT(create_time, ‘%Y%m%d’) date,
count(*) count
FROM
system
GROUP BY
DATE_FORMAT(create_time, ‘%Y%m%d’);

count 的条件查询

查询type字段为SOURCE_SEARCH 的count
count(type= ‘SOURCE_SEARCH’ OR NULL)
count(type= ‘TYPE’ and type2 = ‘TYPE2’ OR NULL)

通过日期分组查询条件下的count

SELECT
DATE_FORMAT(create_time, ‘%Y%m%d’) date,
count(type= ‘TYPE’ OR NULL) count
FROM
system
GROUP BY
DATE_FORMAT(create_time, ‘%Y%m%d’);