您的位置：首页 > 数据库 > mysql  >  Mysql按条件计数多种实现方法详解

Mysql按条件计数多种实现方法详解

 更新时间：2020年7月16日 12:34  点击：1347

最近在给某网站的后台添加一系列的统计功能，遇到很多需要按条件计数的情况。尝试了几种方法，下面简要记录，供大家参考。

问题描述

为使讨论简单易懂，我将问题稍作简化，去掉诸多的背景。

从前有一个皇帝，他有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来得简单便捷。

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持猪先飞。

[!--infotagslink--]

相关文章

  • MySQL性能监控软件Nagios的安装及配置教程

    这篇文章主要介绍了MySQL性能监控软件Nagios的安装及配置教程,这里以CentOS操作系统为环境进行演示,需要的朋友可以参考下...2015-12-14

  • 详解Mysql中的JSON系列操作函数

    新版 Mysql 中加入了对 JSON Document 的支持，可以创建 JSON 类型的字段，并有一套函数支持对JSON的查询、修改等操作，下面就实际体验一下...2016-08-23

  • MyBatisPlus-QueryWrapper多条件查询及修改方式

    这篇文章主要介绍了MyBatisPlus-QueryWrapper多条件查询及修改方式，具有很好的参考价值，希望对大家有所帮助。如有错误或未考虑完全的地方，望不吝赐教...2022-06-27

  • 深入研究mysql中的varchar和limit(容易被忽略的知识)

    为什么标题要起这个名字呢？commen sence指的是那些大家都应该知道的事情，但往往大家又会会略这些东西，或者对这些东西一知半解，今天我总结下自己在mysql中遇到的一些commen sense类型的问题。　...2015-03-15

  • MySQL 字符串拆分操作(含分隔符的字符串截取)

    这篇文章主要介绍了MySQL 字符串拆分操作(含分隔符的字符串截取)，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-22

  • mysql的3种分表方案

    一、先说一下为什么要分表：当一张的数据达到几百万时，你查询一次所花的时间会变多，如果有联合查询的话，有可能会死在那儿了。分表的目的就在于此，减小数据库的负担，缩短查询时间。根据个人经验，mysql执行一个sql的过程如下：1...2014-05-31

  • Windows服务器MySQL中文乱码的解决方法

    我们自己鼓捣mysql时，总免不了会遇到这个问题：插入中文字符出现乱码，虽然这是运维先给配好的环境，但是在自己机子上玩的时候咧，总得知道个一二吧，不然以后如何优雅的吹牛B。...2015-03-15

  • Centos5.5中安装Mysql5.5过程分享

    这几天在centos下装mysql，这里记录一下安装的过程，方便以后查阅Mysql5.5.37安装需要cmake，5.6版本开始都需要cmake来编译，5.5以后的版本应该也要装这个。安装cmake复制代码 代码如下: [root@local ~]# wget http://www.cm...2015-03-15

  • 用VirtualBox构建MySQL测试环境

    宿主机使用网线的时候，客户机在Bridged Adapter模式下，使用Atheros AR8131 PCI-E Gigabit Ethernet Controller上网没问题。 宿主机使用无线的时候，客户机在Bridged Adapter模式下，使用可选项里唯一一个WIFI选项，Microsoft Virtual Wifi Miniport Adapter也无法上网，故弃之。...2013-09-19

  • 忘记MYSQL密码的6种常用解决方法总结

    首先要声明一点，大部分情况下，修改MySQL密码是需要有mysql里的root权限的...2013-09-11

  • MySQL数据库备份还原方法

    MySQL命令行导出数据库： 1，进入MySQL目录下的bin文件夹：cd MySQL中到bin文件夹的目录 如我输入的命令行：cd C:/Program Files/MySQL/MySQL Server 4.1/bin (或者直接将windows的环境变量path中添加该目录) ...2013-09-26

  • Mysql命令大全(详细篇)

    一、连接Mysql格式： mysql -h主机地址 -u用户名 －p用户密码1、连接到本机上的MYSQL。首先打开DOS窗口，然后进入目录mysql/bin，再键入命令mysql -u root -p，回车后提示你输密码.注意用户名前可以有空格也可以没有空格，但是密...2015-11-08

  • Navicat for MySQL 11注册码\激活码汇总

    Navicat for MySQL注册码用来激活 Navicat for MySQL 软件，只要拥有 Navicat 注册码就能激活相应的 Navicat 产品。这篇文章主要介绍了Navicat for MySQL 11注册码\激活码汇总,需要的朋友可以参考下...2020-11-23

  • mysql IS NULL使用索引案例讲解

    这篇文章主要介绍了mysql IS NULL使用索引案例讲解,本篇文章通过简要的案例,讲解了该项技术的了解与使用,以下就是详细内容,需要的朋友可以参考下...2021-08-14

  • 基于PostgreSQL和mysql数据类型对比兼容

    这篇文章主要介绍了基于PostgreSQL和mysql数据类型对比兼容，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...2020-12-25

  • RHEL6.5编译安装MySQL5.6.26教程

    一、准备编译环境，安装所需依赖包yum groupinstall 'Development' -y yum install openssl openssl-devel zlib zlib-devel -y yum install readline-devel pcre-devel ncurses-devel bison-devel cmake -y二、编译安...2015-10-21

  • Mysql中 show table status 获取表信息的方法

    这篇文章主要介绍了Mysql中 show table status 获取表信息的方法的相关资料,需要的朋友可以参考下...2016-03-12

  • 20分钟MySQL基础入门

    这篇文章主要为大家分享了20分钟MySQL基础入门教程，快速掌握MySQL基础知识，真正了解MySQL，具有一定的参考价值，感兴趣的小伙伴们可以参考一下...2016-12-02

  • mongodb与mysql命令详细对比

    传统的关系数据库一般由数据库（database）、表（table）、记录（record）三个层次概念组成，MongoDB是由数据库（database）、集合（collection）、文档对象（document）三个层次组成。MongoDB对于关系型数据库里的表，但是集合中没有列、行和关...2013-09-11

  • node.js如何操作MySQL数据库

    这篇文章主要介绍了node.js如何操作MySQL数据库，帮助大家更好的进行web开发，感兴趣的朋友可以了解下...2020-10-29