简单的mysql按天，按周，按月，按季度，按年统计数据
mysql是常用的数据库，php+mysql是常用的组合，在web开发后台总有一些需要统计的图表、数据。下面是常用的mysql按日、周、月、季查询的方式。条件自己定义。说明：
create_time：是需要查询的时间字段
createTime：是查询的别名
tableName：是需要查询的表名
id、payPrice、refundMoney：仅代表某些字段。
SQL语句如下：
/*查询2小时前的数据*/
SELECT *
FROM tableName
WHEREcreate_time < DATE_SUB(NOW(), INTERVAL 2 HOUR)
ORDER BY create_time DESC;
/*按天统计*/
SELECT
count(id) countNum,
DATE(create_time) createTime
FROM
tableName
GROUP BY
DATE(create_time)
ORDER BY
DATE(create_time) DESC;
/*按周统计*/
SELECT
count(id) countNum,
WEEK(create_time) createTime
FROM
tableName
GROUP BY
WEEK(create_time)
ORDER BY
WEEK(create_time) DESC;
/*按月统计*/
SELECT
count(id) countNum,
MONTH(create_time) createTime
FROM
tableName
GROUP BY
MONTH(create_time)
ORDER BY
MONTH(create_time) DESC;
/*按季度统计*/
SELECT
count(id) countNum,
QUARTER(create_time) createTime
FROM
tableName
GROUP BY
QUARTER(create_time)
ORDER BY
QUARTER(create_time) DESC;
/*按年统计*/
SELECT
count(id) countNum,
YEAR(create_time) createTime
FROM
tableName
GROUP BY
YEAR(create_time)
ORDER BY
YEAR(create_time) DESC;
页:
[1]