Mysql統計總結 - 最近30天，昨天的資料統計

-- 最近30天的醫說釋出數量

SELECT substr(a.feed_publish_time,6, 5) AS '日期', count(*) AS '醫說數' FROM xm_feed a WHERE

DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= a.feed_publish_time GROUP BY substr(a.feed_publish_time,1, 10);

-- 最近30天的醫說評論釋出數量

SELECT substr(a.comm_create_time,6, 5) AS '日期', count(*) AS '醫說評論數' FROM xm_feed_comment a WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= a.comm_create_time GROUP BY substr(a.comm_create_time,1, 10);

-- 最近30天的醫說擴散釋出數量

SELECT substr(a.repost_create_time,6, 5) AS '日期', count(*) AS '醫說評論數' FROM xm_feed_repost a WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= a.repost_create_time GROUP BY substr(a.repost_create_time,1, 10);

-- 最近30天的醫說點贊釋出數量

SELECT substr(a.digg_create_time,6, 5) AS '日期', count(*) AS '醫說評論數' FROM xm_feed_digg a WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= a.digg_create_time GROUP BY substr(a.digg_create_time,1, 10);

-- 昨天的資料

SELECT count(*) as num FROM xm_user a WHERE a.user_audit_status = 'AUDITED' AND date_sub(current_date(),interval 1 day) = substr(a.user_create_time,1, 10);

SELECT count(*) as num FROM xm_user a WHERE date_sub(current_date(),interval 1 day) = substr(a.user_create_time,1, 10);

SELECT count(*) as num FROM xm_feed a WHERE date_sub(current_date(),interval 1 day) = substr(a.feed_publish_time,1, 10);

SELECT count(*) as num FROM xm_feed_comment a WHERE date_sub(current_date(),interval 1 day) = substr(a.comm_create_time,1, 10);

SELECT count(*) as num FROM xm_feed_repost a WHERE date_sub(current_date(),interval 1 day) = substr(a.repost_create_time,1, 10);

SELECT count(*) as num FROM xm_feed_digg a WHERE date_sub(current_date(),interval 1 day) = substr(a.digg_create_time,1, 10);

如何聯系我：【萬裡虎】www.bravetiger.cn

【QQ】3396726884 （咨詢問題100元起，幫助解決問題500元起）

【部落格】http://www.cnblogs.com/kenshinobiy/