MySQL统计查询，日期查询

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"