MySQL查询 每年 每月 每日 订单数和订单金额

mysql函数

1. FROM_UNIXTIME（）函数时间戳转换时间

SELECT FROM_UNIXTIME(1588238359) AS 时间;

2. year（）获取时间的年份

SELECT YEAR('2020-04-30 17：19：19') AS 年;

3. month（）获取时间的月份

SELECT MONTH('2020-04-30 17：19：19') AS 月;

4. day（）获取时间的日

SELECT DAY('2020-04-30 17：19：19') AS 日;

下面查询统计 "每年" 的订单数和订单总金额（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在数据库为时间戳）

-- 下面查询统计每月的订单（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在数据库为时间戳）

-- 下面查询统计每日的订单（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));

例1：统计每月的销售总金额

说明：

1. 字段createTime 当前系统时间格式为yyyy-MM-dd HH:mm:ss；
2. 字段 price 销售额；
3. CASE MONTH(createTime) WHEN '8' THEN price ELSE 0 end：获取字段createTime时间的月份，当月份为8月，获取8月所有的销售额，若没有销售额销售额则为0；
4. SUM(CASE MONTH(createTime) WHEN '8' THEN price ELSE 0 END)：将8月所有的销售额进行合计；
5. IFNULL(SUM(CASE MONTH(createTime) WHEN '8' THEN price ELSE 0 END), 0) AS 八月份  ：类似三元运算符，IFNULL(当月总销售额,0)，假如销售额不为 NULL，则 IFNULL() 的返回值为当月总销售额，否则为0，AS别名为'八月份'；
6. #{year}：要查询的年份；

select 
	IFNULL(SUM(CASE MONTH(createTime) WHEN '1' THEN price ELSE 0 END), 0) AS 一月份,
	IFNULL(SUM(CASE MONTH(createTime) WHEN '2' THEN price ELSE 0 END), 0) AS 二月份,
	IFNULL(SUM(CASE MONTH(createTime) WHEN '3' THEN price ELSE 0 END), 0) AS 三月份,
	IFNULL(SUM(CASE MONTH(createTime) WHEN '4' THEN price ELSE 0 END), 0) AS 四月份,
	IFNULL(SUM(CASE MONTH(createTime) WHEN '5' THEN price ELSE 0 END), 0) AS 五月份,
	IFNULL(SUM(CASE MONTH(createTime) WHEN '6' THEN price ELSE 0 END), 0) AS 六月份,
	IFNULL(SUM(CASE MONTH(createTime) WHEN '7' THEN price ELSE 0 END), 0) AS 七月份,
	IFNULL(SUM(CASE MONTH(createTime) WHEN '8' THEN price ELSE 0 END), 0) AS 八月份,
	IFNULL(SUM(CASE MONTH(createTime) WHEN '9' THEN price ELSE 0 END), 0) AS 九月份,
	IFNULL(SUM(CASE MONTH(createTime) WHEN '10' THEN price ELSE 0 END), 0) AS 十月份,
	IFNULL(SUM(CASE MONTH(createTime) WHEN '11' THEN price ELSE 0 END), 0) AS 十一月份,
	IFNULL(SUM(CASE MONTH(createTime) WHEN '12' THEN price ELSE 0 END), 0) AS 十二月份
FROM `order`
WHERE YEAR(createTime)=#{year};

当年每月销售总额结果为：

例2：统计每月的销售总金额

-- 统计每月销售总金额
SELECT 
  SUM(price),
  cTime 
FROM
  (SELECT 
    oid,
    price,
    DATE_FORMAT(createtime, '%y-%c') cTime 
  FROM
    `order`) AS o 
GROUP BY cTime ;

当年每月销售总额结果为：

 

转载请注明出处：BestEternity亲笔。