Mysql中根据不同的条件统计汇总数据
程序员文章站
2022-06-23 08:22:20
Mysql中根据不同的条件统计汇总数据首先抛出问题:根据不同订单状态查询查询订单个数以及汇总金额,最终结果在一条记录中展示。为了演示首先建表添加数据新建一张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...
Mysql中根据不同的条件统计汇总数据
首先抛出问题:根据不同订单状态查询查询订单个数以及汇总金额,最终结果在一条记录中展示。
为了演示首先建表添加数据
- 新建一张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;
- 添加数据
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 语句有两种写法
- 第一种
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list] END CASE
- 第二种
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE
分别就这两种写法来做个例子
SELECT
* ,
(CASE `status` WHEN '0' THEN '创建'
WHEN '1' THEN '待付款'
WHEN '2' THEN '待发货'
WHEN '3' THEN '待收货'
ELSE '未知状态' END) AS 状态
FROM
`order`;
查询结果如下
SELECT
* ,
(CASE WHEN `amount`<= 3000 THEN '低'
WHEN `amount`<= 5000 THEN '中'
WHEN `amount`<= 10000 THEN '高'
ELSE '未知区间' END) AS 价格等级
FROM
`order`;
查询结果如下
看到这里相信大家肯定对MySql中CASE语句的使用更加了解了,在今后的应用中遇到相关的问题脑海中肯定会有解决的思路了。
本文地址:https://blog.csdn.net/JonLoveJane/article/details/110672027
上一篇: 为何说官渡之战结束以后,曹操并没有获得多大的优势?
下一篇: python基础练习题