首先介绍几个mysql函数
FROM_UNIXTIME()函数时间戳转换时间
SELECT FROM_UNIXTIME(1588238359) AS 时间
year()获取时间的年份
SELECT YEAR(‘2020-04-30 17:19:19’) AS 年
month()获取时间的月份
SELECT MONTH(‘2020-04-30 17:19:19’) AS 月
day()获取时间的日
SELECT DAY(‘2020-04-30 17:19:19’) AS 日
下面查询统计每年的订单(createtime在数据库为时间戳)
数量
SELECT year(FROM_UNIXTIME(createtime)) 年,COUNT(*) FROM order WHERE 1 group by year(FROM_UNIXTIME(createtime))
金额
SELECT YEAR(FROM_UNIXTIME(createtime)) 年,SUM(price) FROM order WHERE 1 GROUP BY YEAR(FROM_UNIXTIME(createtime))
下面查询统计每年的订单(createtime在数据库为时间戳)
数量
SELECT year(FROM_UNIXTIME(createtime)) 年,COUNT(*) FROM order WHERE 1 group by year(FROM_UNIXTIME(createtime))
金额
SELECT YEAR(FROM_UNIXTIME(createtime)) 年,SUM(price) FROM order WHERE 1 GROUP BY YEAR(FROM_UNIXTIME(createtime))
下面查询统计每月的订单(createtime在数据库为时间戳)
数量
SELECT YEAR(FROM_UNIXTIME(createtime)) 年,MONTH(FROM_UNIXTIME(createtime)) 月,COUNT(*) FROM order WHERE 1 GROUP BY YEAR(FROM_UNIXTIME(createtime)),MONTH(FROM_UNIXTIME(createtime))
金额
SELECT YEAR(FROM_UNIXTIME(createtime)) 年,MONTH(FROM_UNIXTIME(createtime)) 月,SUM(price) FROM order WHERE 1 GROUP BY YEAR(FROM_UNIXTIME(createtime)),MONTH(FROM_UNIXTIME(createtime))
下面查询统计每日的订单(createtime在数据库为时间戳)
数量
SELECT YEAR(FROM_UNIXTIME(createtime)) 年,MONTH(FROM_UNIXTIME(createtime)) 月,DAY(FROM_UNIXTIME(createtime)) 日,COUNT(*) FROM order WHERE 1 GROUP BY YEAR(FROM_UNIXTIME(createtime)),MONTH(FROM_UNIXTIME(createtime)),DAY(FROM_UNIXTIME(createtime))
金额
SELECT YEAR(FROM_UNIXTIME(createtime)) 年,MONTH(FROM_UNIXTIME(createtime)) 月,DAY(FROM_UNIXTIME(createtime)) 日,,SUM(price) FROM order WHERE 1 GROUP BY YEAR(FROM_UNIXTIME(createtime)),MONTH(FROM_UNIXTIME(createtime)),DAY(FROM_UNIXTIME(createtime))