统计当天日期信息发布总数(curdate()函数---当天)
select count(bi.information_id) from bbg_informationbi
where date(bi.release_time) = curdate()
统计前一天的信息发布总数date_sub(curdate(),interval 1 day);)
select count(bi.information_id) from bbg_informationbi
where date(bi.release_time) =date_sub(curdate(),interval 1 day);
统计前一个月的信息发布总数
select count(bi.information_id) from bbg_informationbi
where date(bi.release_time) =date_sub(curdate(),interval 1 month);
统计后一个年的信息发布总数
select count(bi.information_id) from bbg_informationbi
where date(bi.release_time) = date_sub(curdate(),interval 1 year);
统计从昨天开端统计前7天的信息发布总数包含昨天
select count(bi.information_id) from bbg_informationbi
where date(bi.release_time) >=date_sub(curdate(),interval 7 day)
and DATE(bi.release_time) <= date_sub(CURDATE(),INTERVAL 1 DAY)
统计某一天信息发布总数
select count(bi.information_id) from bbg_information bi
where date(bi.release_time) = DATE_SUB("2017-12-6",INTERVAL 0 DAY)
根据一段时间,每天的发布数统计(注意:只查询有发布数的日期,DATE_FORMAT格式化日期)
SELECT
count(*),
DATE_FORMAT(bi.release_time, '%Y-%m-%d') AS t
FROM
bbg_information bi
WHERE
DATE_FORMAT(bi.release_time, '%Y-%m-%d') >= '2016-11-01' and DATE_FORMAT(bi.release_time,'%Y-%m-%d') <='2017-12-7'
GROUP BY
t
ORDER BY t desc