mysql按时间统计

1.函数

DATE_FORMAT（now()，'格式'）  格式化时间 %y-%m-%d---%Y-%m-%d

DATE_SUB(CURRENT_DATE , INTERVAL #{num_value} DAY)  当前日期减去num_value天

2.按月份统计

SELECT DATE_FORMAT(pubtime,'%Y-%m') MONTH,COUNT(1) COUNT FROM (
		SELECT url,fetch_time,domain,pubtime,title,TEXT,conllectionId,warningId,dailyId 
		FROM (SELECT b.url ,b.fetch_time,
		GROUP_CONCAT(CASE WHEN b.CODE = 'domain' THEN text_value ELSE NULL END) AS domain,
		GROUP_CONCAT(CASE WHEN b.CODE = 'pubtime' THEN text_value ELSE NULL END) AS pubtime,
		GROUP_CONCAT(CASE WHEN b.CODE = 'title' THEN text_value ELSE NULL END) AS title,
		GROUP_CONCAT(CASE WHEN b.CODE = 'text' THEN text_value ELSE NULL END)
		AS TEXT,
		GROUP_CONCAT(CASE WHEN b.CODE = 'html' THEN text_value ELSE NULL END) AS html
		,a.crawl_data_id conllectionId,c.crawl_data_id warningId,d.crawl_data_id dailyId
		FROM crawl_data b
		LEFT JOIN  conllection_info a
		ON  b.url = a.crawl_data_id
		LEFT JOIN warning_info c
		ON b.url = c.crawl_data_id
		LEFT JOIN  daily_info d
		ON b.url = d.crawl_data_id
		GROUP BY b.url,b.fetch_time,a.crawl_data_id,c.crawl_data_id,d.crawl_data_id) crawl_data
		WHERE 1 = 1 AND warningId IS NOT NULL AND 
		url IS NOT NULL OR title IS NOT NULL OR TEXT IS NOT NULL OR pubtime IS NOT NULL) a 
		GROUP BY DATE_FORMAT(pubtime,'%Y%m') 

 3.按日期统计

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d') DAY, COUNT(1) nowDayCount FROM (
		SELECT url,fetch_time,domain,pubtime,title,TEXT,conllectionId,warningId,dailyId 
		FROM (SELECT b.url ,b.fetch_time,
		GROUP_CONCAT(CASE WHEN b.CODE = 'domain' THEN text_value ELSE NULL END) AS domain,
		GROUP_CONCAT(CASE WHEN b.CODE = 'pubtime' THEN text_value ELSE NULL END) AS pubtime,
		GROUP_CONCAT(CASE WHEN b.CODE = 'title' THEN text_value ELSE NULL END) AS title,
		GROUP_CONCAT(CASE WHEN b.CODE = 'text' THEN text_value ELSE NULL END)
		AS TEXT,
		GROUP_CONCAT(CASE WHEN b.CODE = 'html' THEN text_value ELSE NULL END) AS html
		,a.crawl_data_id conllectionId,c.crawl_data_id warningId,d.crawl_data_id dailyId
		FROM crawl_data b
		LEFT JOIN  conllection_info a
		ON  b.url = a.crawl_data_id
		LEFT JOIN warning_info c
		ON b.url = c.crawl_data_id
		LEFT JOIN  daily_info d
		ON b.url = d.crawl_data_id
		GROUP BY b.url,b.fetch_time,a.crawl_data_id,c.crawl_data_id,d.crawl_data_id) crawl_data
		WHERE 1 = 1 
		<if test="num_value != null and num_value != ''" >
	       DATE_FORMAT(pubtime, '%Y-%m-%d' ) BETWEEN  DATE_SUB(CURRENT_DATE , INTERVAL #{num_value} DAY)  AND CURRENT_DATE
		</if> 
		limit 0, 1