文档章节

Mysql按条件计数的几种方法

2shou2shou
 2shou2shou
发布于 2012/11/28 09:42
字数 1317
阅读 91
收藏 6
最近在给喜乐喜乐网的后台添加一系列的统计功能,遇到很多需要按条件计数的情况。尝试了几种方法,下面简要记录,供大家参考。
问题描述
为使讨论简单易懂,我将问题稍作简化,去掉诸多的背景。
从前有一个皇帝,他有50个妃子,这些妃子很没有天理的给他生了100,000个儿子,于是,皇帝很苦恼,海量的儿子很难管理,而且,他想知道每个妃子给他生了多少个儿子,从而论功行赏,这很难办。于是,皇帝请了一个程序员帮他编了一个程序,用数据库来存储所有的儿子的信息,这样就可以用程序来统计和管理啦。
数据库的结构如下:
id 皇子的唯一编号
mother 皇子母亲的唯一编号
皇帝把妃子分成了两个等级,天宫娘娘(编号小于25)和地宫娘娘(编号大于等于25),他想知道天宫娘娘们和地宫娘娘们的生育能力孰强孰弱。于是,程序员开始写SQL Query了。
方法1:使用GROUP BY
SQL Query
SELECT COUNT(*) FROM `prince` GROUP BY `mother` > 24;
执行结果
count(*)
50029
49971
在100,000行数据上的运行时间:0.0335 秒
分析
这种GROUP BY方法的最大问题在于:无法区分所得到的结果。这两个数字哪一个是天宫娘娘们所生的皇子数,哪一个是地宫娘娘们所生的皇子数呢?不知道。所以,尽管它统计出了总数,但是没有什么意义。
因此,为了区分统计结果,必须要把条件 mother > 24 也作为一个字段在结果集中作为一个字段体现出来,修改后的sql如下:
SELECT COUNT(*) AS `number`, `mother` > 24 AS `type` FROM `prince` GROUP BY `mother` > 24;
执行结果
number  type
50029   0
49971   1
条件表达式作为字段时,该字段的值就是该条件表达式的值,因此,对应我们的例子,type = 1 也就是表示 mother > 24 的值为1,因此,第二行中的数字代表地宫娘娘们所生的皇子数。
经过修改后,我们看出,天宫娘娘们略胜一筹。
优缺点
缺点是显而易见的,由于使用了条件表达式作为分组依据,它只能做二元的划分,对于要分成多类进行统计的情况不能够胜任。比如要分别统计1~10号、11~24号,25号~50号妃子的产子数,就无法实现了。
另外,由于使用了GROUP BY,因此涉及到排序,执行时间上要更长。
我暂时没有发现这种方法的优点。
方法2:使用嵌套的SELECT
使用嵌套的SELECT也可以达到目的,在每个SELECT子句中统计一个条件下的数据,然后用一个主SELECT把这些统计数据整合起来。
SQL Query
SELECT 
    ( SELECT COUNT( * ) FROM `prince` WHERE `mother` >24 ) AS `digong`,
    ( SELECT COUNT( * ) FROM `prince` WHERE `mother` <=24 ) AS `tiangong`
执行结果
digong  tiangong
49971   50029
在100,000行数据上的运行时间:0.0216 秒
分析
这种嵌套SELECT的方法非常直观,就是分别统计各个条件下的数值,最后进行汇总,通俗易懂,跟自然语言没啥区别了。
优缺点
优点就是直观,而且速度也比GROUP BY要快。虽然是3条SELECT语句,看起来比GROUP BY的方案多了2条语句,但是它不涉及到排序,这就节省了很多时间。
缺点可能就是语句稍多,对语句数量有洁癖的同学可能会比较不舒服。
方法3:使用CASE WHEN
CASE WHEN语句的功能很强大,可以定义灵活的查询条件,很适合进行分类统计。
SQL Query
SELECT 
    COUNT( CASE WHEN `mother` >24 THEN 1 ELSE NULL END ) AS `digong`,
    COUNT( CASE WHEN `mother` <=24 THEN 1 ELSE NULL END ) AS `tiangong`
FROM prince
执行结果
digong  tiangong
49971   50029
在100,000行数据上的运行时间:0.02365825 秒
分析
此方法的关键在于
COUNT( CASE WHEN `mother` >24 THEN 1 ELSE NULL END )
这里的COUNT和CASE WHEN联合使用,做到了分类计数。先使用CASE WHEN,当满足条件时,将字段值设置为 1, 不满足条件时,将字段值设置为NULL,接着COUNT函数仅对非NULL字段进行计数,于是,问题解决。
优缺点
优点嘛,此方法也不涉及到排序,因此运行时间上与方法2相当,SELECT语句减少到了 1 条。
缺点就是语句比较长,对语句长度有洁癖的同学可能会比较不舒服。
总结
对于确定分类的按条件计数,可以尽量不用GROUP BY,从而避免排序动作,加速Query的执行。
如果需要根据某个字段的值进行分类,而该字段的值是可变的,比如皇帝要统计每一个妃子的产子数,而他可能不停的再娶很多妃子,这种情况下,使用方法2和方法3就不太灵光了,还是使用一个GROUP BY来得简单便捷。

本文转载自:http://www.pureweber.com/article/mysql-conditional-count/

2shou2shou
粉丝 1
博文 33
码字总数 882
作品 2
广州
程序员
私信 提问
MySQL从删库到跑路

数据库(Database)是按照数据结构来组织、存储和管理数据的仓库, 每个数据库都有一个或多个不同的API用于创建,访问,管理,搜索和复制所保存的数据。 我们也可以将数据存储在文件中,但是...

闻人牧月
2017/11/03
0
0
Navicat使用教程:获取MySQL中的行数(第1部分)

下载Navicat Premium最新版本 Navicat Premium是一个可连接多种数据库的管理工具,它可以让你以单一程序同时连接到MySQL、Oracle及PostgreSQL数据库,让管理不同类型的数据库更加的方便。 在...

电池盒
02/13
25
0
spring boot开发笔记——mybatis

概述   mybatis框架的优点,就不用多说了,今天这边干货主要讲mybatis的逆向工程,以及springboot的集成技巧,和分页的使用   因为在日常的开发中,当碰到特殊需求之类会手动写一下sql语...

诸葛西门
2018/06/05
0
0
Linq的一些操作符-图表展示

对数据进行排序 Set 运算 Distinct Except Intersect Union Filtering Data 限定符操作 数据分区 联接运算 数据分组 生成操作 元素操作 Converting Data Types 串联运算 聚合操作 本文转自L...

嗯哼9925
2017/11/09
0
0
MySQL · 功能分析 · MySQL表定义缓存

表定义 MySQL的表包含表名,表空间、索引、列、约束等信息,这些表的元数据我们暂且称为表定义信息。 对于InnoDB来说,MySQL在server层和engine层都有表定义信息。server层的表定义记录在frm...

阿里云RDS-数据库内核组
2015/08/09
0
0

没有更多内容

加载失败,请刷新页面

加载更多

计算机实现原理专题--二进制减法器(二)

在计算机实现原理专题--二进制减法器(一)中说明了基本原理,现准备说明如何来实现。 首先第一步255-b运算相当于对b进行按位取反,因此可将8个非门组成如下图的形式: 由于每次做减法时,我...

FAT_mt
昨天
6
0
好程序员大数据学习路线分享函数+map映射+元祖

好程序员大数据学习路线分享函数+map映射+元祖,大数据各个平台上的语言实现 hadoop 由java实现,2003年至今,三大块:数据处理,数据存储,数据计算 存储: hbase --> 数据成表 处理: hive --> 数...

好程序员官方
昨天
7
0
tabel 中含有复选框的列 数据理解

1、el-ui中实现某一列为复选框 实现多选非常简单: 手动添加一个el-table-column,设type属性为selction即可; 2、@selection-change事件:选项发生勾选状态变化时触发该事件 <el-table @sel...

everthing
昨天
6
0
【技术分享】TestFlight测试的流程文档

上架基本需求资料 1、苹果开发者账号(如还没账号先申请-苹果开发者账号申请教程) 2、开发好的APP 通过本篇教程,可以学习到ios证书申请和打包ipa上传到appstoreconnect.apple.com进行TestF...

qtb999
昨天
10
0
再见 Spring Boot 1.X,Spring Boot 2.X 走向舞台中心

2019年8月6日,Spring 官方在其博客宣布,Spring Boot 1.x 停止维护,Spring Boot 1.x 生命周期正式结束。 其实早在2018年7月30号,Spring 官方就已经在博客进行过预告,Spring Boot 1.X 将维...

Java技术剑
昨天
18
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部