Mysql中根据不同的条件统计汇总数据

首先抛出问题：根据不同订单状态查询查询订单个数以及汇总金额，最终结果在一条记录中展示。

为了演示首先建表添加数据

1. 新建一张order表

DROP TABLE IF EXISTS `order`;
CREATE TABLE `order` (
  `id` int(11) NOT NULL COMMENT 'Id',
  `category` varchar(255) DEFAULT NULL COMMENT '分类',
  `status` varchar(2) DEFAULT NULL COMMENT '状态(0:创建,1:待付款,2:待发货,3:待收货)',
  `amount` decimal(10,2) DEFAULT NULL COMMENT '金额',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 添加数据

INSERT INTO `order` VALUES (0, '华为', '0', 5500.00);
INSERT INTO `order` VALUES (1, '小米', '2', 3000.00);
INSERT INTO `order` VALUES (2, '华为', '1', 5500.00);
INSERT INTO `order` VALUES (3, '苹果', '2', 5000.00);
INSERT INTO `order` VALUES (4, '华为', '1', 5500.00);
INSERT INTO `order` VALUES (5, '小米', '2', 3000.00);
INSERT INTO `order` VALUES (6, '华为', '0', 5500.00);
INSERT INTO `order` VALUES (7, '苹果', '1', 5000.00);
INSERT INTO `order` VALUES (8, '小米', '3', 3000.00);
INSERT INTO `order` VALUES (9, '华为', '3', 5500.00);

接下来演示各种统计问题

1. 查询出订单表中各个状态分别有多少笔订单?

SELECT
   COUNT( CASE WHEN `status` = '0' THEN 1 ELSE NULL END ) AS 创建, 
   COUNT( CASE WHEN `status` = '1' THEN 1 ELSE NULL END ) AS 待付款,
   COUNT( CASE WHEN `status` = '2' THEN 1 ELSE NULL END ) AS 待发货,
   COUNT( CASE WHEN `status` = '3' THEN 1 ELSE NULL END ) AS 待收货
FROM
   `order`;

查询结果如下

2. 查询出订单表中各个状态分别有多少笔订单，并且汇总每种状态下订单金额?

SELECT
	COUNT( CASE WHEN `status` = '0' THEN 1 ELSE NULL END ) AS 创建, 
	COUNT( CASE WHEN `status` = '1' THEN 1 ELSE NULL END ) AS 待付款,
	COUNT( CASE WHEN `status` = '2' THEN 1 ELSE NULL END ) AS 待发货,
	COUNT( CASE WHEN `status` = '3' THEN 1 ELSE NULL END ) AS 待收货,
	SUM( CASE WHEN `status` = '0' THEN IFNULL(`amount`,0) ELSE 0 END) AS 创建总金额,
	SUM( CASE WHEN `status` = '1' THEN IFNULL(`amount`,0) ELSE 0 END) AS 待付款总金额,
	SUM( CASE WHEN `status` = '2' THEN IFNULL(`amount`,0) ELSE 0 END) AS 待发货总金额,
	SUM( CASE WHEN `status` = '3' THEN IFNULL(`amount`,0) ELSE 0 END) AS 待收货总金额
FROM
	`order`;

查询结果如下

3. 查询出订单表中每种分类下各个状态分别有多少笔订单，并且汇总每种状态下订单金额?

SELECT
	`category` as 分类,
	COUNT( CASE WHEN `status` = '0' THEN 1 ELSE NULL END ) AS 创建, 
	COUNT( CASE WHEN `status` = '1' THEN 1 ELSE NULL END ) AS 待付款,
	COUNT( CASE WHEN `status` = '2' THEN 1 ELSE NULL END ) AS 待发货,
	COUNT( CASE WHEN `status` = '3' THEN 1 ELSE NULL END ) AS 待收货,
	SUM( CASE WHEN `status` = '0' THEN IFNULL(`amount`,0) ELSE 0 END) AS 创建总金额,
	SUM( CASE WHEN `status` = '1' THEN IFNULL(`amount`,0) ELSE 0 END) AS 待付款总金额,
	SUM( CASE WHEN `status` = '2' THEN IFNULL(`amount`,0) ELSE 0 END) AS 待发货总金额,
	SUM( CASE WHEN `status` = '3' THEN IFNULL(`amount`,0) ELSE 0 END) AS 待收货总金额
FROM
	`order`
GROUP BY `category`;

查询结果如下

演示到这里是不是感觉这种类型的数据展示以后能轻松搞定了，非常实用的Sql。
顺便来拓展一个Mysql CASE 语句

CASE 语句有两种写法

1. 第一种

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list] END CASE

2. 第二种

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

分别就这两种写法来做个例子

1. 第一种写法---->就上述的订单表中订单状态做一个简单的字典翻译

SELECT
	* ,
	(CASE `status` WHEN '0' THEN '创建' 
	WHEN '1' THEN '待付款'
	WHEN '2' THEN '待发货'
	WHEN '3' THEN '待收货'
	ELSE '未知状态' END) AS 状态
FROM
	`order`;

查询结果如下

1. 第二种写法---->就上述的订单表中手机的价格做一个等级描述

SELECT
	* ,
	(CASE WHEN `amount`<= 3000 THEN '低' 
	WHEN `amount`<= 5000 THEN '中'
	WHEN `amount`<= 10000 THEN '高'
	ELSE '未知区间' END) AS 价格等级
FROM
	`order`;

查询结果如下

看到这里相信大家肯定对MySql中CASE语句的使用更加了解了，在今后的应用中遇到相关的问题脑海中肯定会有解决的思路了。

参考MySql CASE语句

本文地址：https://blog.csdn.net/JonLoveJane/article/details/110672027