MySQL统计查询,日期查询
注:subscribe 为表,CREATE_TIME 为表中的时间字段
#统计查询某一个类型共有几人
#第一种写法low
SELECT COUNT(*) AS '1' FROM subscribe s WHERE AUDIT_STATUS='1';
SELECT COUNT(*) AS '4' FROM subscribe s WHERE AUDIT_STATUS='4';
SELECT COUNT(*) AS '8' FROM subscribe s WHERE AUDIT_STATUS='8';
#第二种写法 使用sum函数一般般
SELECT
SUM(AUDIT_STATUS='1') AS '1',
SUM(AUDIT_STATUS='4') AS '4',
SUM(AUDIT_STATUS='8') AS '8'
FROM subscribe
#第三种写法,格局,但是不方便前端渲染
SELECT AUDIT_STATUS,Count(*) FROM subscribe GROUP BY AUDIT_STATUS;
#按年月日统计
SELECT CREATE_TIME ,COUNT(*) FROM subscribe GROUP BY DATE_FORMAT(CREATE_TIME,'%Y')
SELECT CREATE_TIME ,COUNT(*) FROM subscribe GROUP BY DATE_FORMAT(CREATE_TIME,'%Y-%m')
SELECT CREATE_TIME ,COUNT(*) FROM subscribe GROUP BY DATE_FORMAT(CREATE_TIME,'%Y-%m-%d')
SELECT CREATE_TIME ,COUNT(*) FROM subscribe GROUP BY DATE_FORMAT(CREATE_TIME,'%Y-%m-%d %H ')
#按时间来查询,今天、昨天、近7天、当月,把条件换成需要查询的日期,可以根据某一个时间查询
#CURDATE() 当前时间
SELECT CREATE_TIME FROM subscribe WHERE TO_DAYS(CREATE_TIME)=TO_DAYS(NOW())
SELECT CREATE_TIME FROM subscribe WHERE TO_DAYS(NOW()) - TO_DAYS(CREATE_TIME)<=1
SELECT CREATE_TIME FROM subscribe where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(CREATE_TIME)
SELECT CREATE_TIME FROM subscribe WHERE DATE_FORMAT(CREATE_TIME, '%Y%m') = DATE_FORMAT( CURDATE(),'%Y%m')
SELECT CREATE_TIME FROM subscribe WHERE YEAR(CREATE_TIME)=YEAR(NOW())
查询某年,某月,某日的数据,接在where后面
DATE_FORMAT(ap.APPRAISE_TIME,'%Y') = "2020"
DATE_FORMAT(ap.APPRAISE_TIME,'%Y-%m') = "2020-12"
DATE_FORMAT(ap.APPRAISE_TIME,'%Y-%m-%d') = "2020-12-01"
查询某段时间
DATE_FORMAT(g.START_TIME,'%Y-%m-%d') BETWEEN "2020-12-01" AND "2021-10-21"