当前位置： 首页 > news >正文

mysql count when_在mysql中使用COUNT 或者SUM函数计算记录总数

news 来源：原创 2024/5/7 14:49:51

count函数的作用

想要真正的理解count函数，我们就必须明白count函数的作用。

作用一：统计某一列非空(not null)值得数量，即统计某列有值得结果数,使用count(col),其中col为设置为not null 的行

作用二：统计结果集的行数，此时不用管某列是否为null值。即使用count(*).

明白了这点，我们就应该知道MySQL的count(*)并不是想象中的那样，统计每一列的值，而是直接忽视掉所有列，直接统计行数，那么它的效率肯定是很高的。

但是有一点，当col指定了该字段为NOT NULL时实际上，MySQL会自动将count(col)转为count(*),但是这样也同样耗费了些时间，如果col没有指定为NOT NULL的话，那么效率就更低了，MySQL就必须要判断每一行的值是否为空。

所以综上所述，最好优先使用select count(*)

当统计某一列等于多少的值得时候可以使用下面两种方法。

#统计出表中id为23的值的数量的两种方法

SELECT SUM(IF(id = 23,1,0)) FROM table

SELECT COUNT(id = 23 OR NULL) FROM table //为了让id不为23的时候，结果为null,而count(null)=0

解释:

查了下资料，count('任意内容')都会统计出所有记录数，因为count只有在遇见null时不计数，即count(null)=0，

因此前者单引号内不管输入什么值都会统计出所有记录数，所以count(id='23')单引号里你填什么年份都是1000条记录，因为他们都not null！

而为了让查找id=23的这个条件生效，就必须加上or null，告诉count，id=23为计数条件且不统计记录为null的值。

Count(NULL)是 不统计数， 至于加上or NULL ， 很像其他编程里的or运算符，第一个表达式是true就是不执行or后面的表达式，第一个表达式是false 执行or后面的表达式 。当id不为23的时候,id= 23 or NULL 的结果是NULL，Count就不会统计上这条记录数。

又如:

SELECT ID FROM EPRJ_LIST WHERE EPRJ_INFO_ID = 78821 AND (NUM >10 OR NULL)

该SQL语句也是查询NUM >10而且NUM不为NULL的语句(纯粹是为了测试,可以不加or null条件的),因为WHERE条件中如果直接写成:WHERE 条件1 AND NULL 或者WHERE NULL(即以NULL作为一个单独的查询条件),那么返回的结果为空集

而count(id> 23 or null)可以这么理解:SELECT COUNT(*) FROM ...WHERE (id> 23 or null)

mysql 中sum (if())与case

先来一个简单的sum

select sum(qty) as total_qty from inventory_product group by product_id

这样就会统计出所有product的qty.

但是很不幸，我们的系统里面居然有qty为负值。而我只想统计那些正值的qty，加上if function就可以了。 SQL为：

select sum(if(qty > 0, qty, 0)) as total_qty   from inventory_product group by product_id

意思是如果qty > 0, 将qty的值累加到total_qty, 否则将0累加到total_qty.

以下是sum(if())的例子,：

select

sum( if( qty > 0, qty, 0)) as total_qty,//用来计算qty >0的所有记录的字段qty值的汇总(从0累加,每次累加qty)

sum( if( qty < 0, 1, 0 )) as negative_qty_count//计算qty < 0的记录条数(从0累加,每次累加1)

from inventory_product group by product_id

上面主要介绍sum (if())的定义，以下是关于数据库行列转换的一些方法整理，也是拷贝其他网友的博客的，做了一些删减。

现整理解法如下：

数据样本：

create table tx(

id int primary key,

c1 char(2),

c2 char(2),

c3 int

);

insert into tx values

(1 ,'A1','B1',9),

(2 ,'A2','B1',7),

(3 ,'A3','B1',4),

(4 ,'A4','B1',2),

(5 ,'A1','B2',2),

(6 ,'A2','B2',9),

(7 ,'A3','B2',8),

(8 ,'A4','B2',5),

(9 ,'A1','B3',1),

(10 ,'A2','B3',8),

(11 ,'A3','B3',8),

(12 ,'A4','B3',6),

(13 ,'A1','B4',8),

(14 ,'A2','B4',2),

(15 ,'A3','B4',6),

(16 ,'A4','B4',9),

(17 ,'A1','B4',3),

(18 ,'A2','B4',5),

(19 ,'A3','B4',2),

(20 ,'A4','B4',5);

mysql> select * from tx;

+----+------+------+------+

| id | c1   | c2   | c3   |

+----+------+------+------+

|  1 | A1   | B1   |    9 |

|  2 | A2   | B1   |    7 |

|  3 | A3   | B1   |    4 |

|  4 | A4   | B1   |    2 |

|  5 | A1   | B2   |    2 |

|  6 | A2   | B2   |    9 |

|  7 | A3   | B2   |    8 |

|  8 | A4   | B2   |    5 |

|  9 | A1   | B3   |    1 |

| 10 | A2   | B3   |    8 |

| 11 | A3   | B3   |    8 |

| 12 | A4   | B3   |    6 |

| 13 | A1   | B4   |    8 |

| 14 | A2   | B4   |    2 |

| 15 | A3   | B4   |    6 |

| 16 | A4   | B4   |    9 |

| 17 | A1   | B4   |    3 |

| 18 | A2   | B4   |    5 |

| 19 | A3   | B4   |    2 |

| 20 | A4   | B4   |    5 |

+----+------+------+------+

20 rows in set (0.00 sec)

mysql>

期望结果

+------+-----+-----+-----+-----+------+

|C1    |B1   |B2   |B3   |B4   |Total |

+------+-----+-----+-----+-----+------+

|A1    |9    |2    |1    |11   |23    |

|A2    |7    |9    |8    |7    |31    |

|A3    |4    |8    |8    |8    |28    |

|A4    |2    |5    |6    |14   |27    |

|Total |22   |24   |23   |40   |109   |

+------+-----+-----+-----+-----+------+

1. 利用SUM(IF()) 生成列 + WITH ROLLUP 生成汇总行,并利用 IFNULL将汇总行标题显示为 Total

mysql> SELECT

->     IFNULL(c1,'total') AS total,

->     SUM(IF(c2='B1',c3,0)) AS B1,

->     SUM(IF(c2='B2',c3,0)) AS B2,

->     SUM(IF(c2='B3',c3,0)) AS B3,

->     SUM(IF(c2='B4',c3,0)) AS B4,

->     SUM(IF(c2='total',c3,0)) AS total

-> FROM (

->     SELECT c1,IFNULL(c2,'total') AS c2,SUM(c3) AS c3

->     FROM tx

->     GROUP BY c1,c2

->     WITH ROLLUP

->     HAVING c1 IS NOT NULL

-> ) AS A

-> GROUP BY c1

-> WITH ROLLUP;

+-------+------+------+------+------+-------+

| total | B1   | B2   | B3   | B4   | total |

+-------+------+------+------+------+-------+

| A1    |    9 |    2 |    1 |   11 |    23 |

| A2    |    7 |    9 |    8 |    7 |    31 |

| A3    |    4 |    8 |    8 |    8 |    28 |

| A4    |    2 |    5 |    6 |   14 |    27 |

| total |   22 |   24 |   23 |   40 |   109 |

+-------+------+------+------+------+-------+

5 rows in set, 1 warning (0.00 sec)

2. 利用SUM(IF()) 生成列 + UNION 生成汇总行,并利用 IFNULL将汇总行标题显示为 Total

mysql> select c1,

-> sum(if(c2='B1',C3,0)) AS B1,

-> sum(if(c2='B2',C3,0)) AS B2,

-> sum(if(c2='B3',C3,0)) AS B3,

-> sum(if(c2='B4',C3,0)) AS B4,SUM(C3) AS TOTAL

-> from tx

-> group by C1

-> UNION

-> SELECT 'TOTAL',sum(if(c2='B1',C3,0)) AS B1,

-> sum(if(c2='B2',C3,0)) AS B2,

-> sum(if(c2='B3',C3,0)) AS B3,

-> sum(if(c2='B4',C3,0)) AS B4,SUM(C3) FROM TX

-> ;

+-------+------+------+------+------+-------+

| c1    | B1   | B2   | B3   | B4   | TOTAL |

+-------+------+------+------+------+-------+

| A1    |    9 |    2 |    1 |   11 |    23 |

| A2    |    7 |    9 |    8 |    7 |    31 |

| A3    |    4 |    8 |    8 |    8 |    28 |

| A4    |    2 |    5 |    6 |   14 |    27 |

| TOTAL |   22 |   24 |   23 |   40 |   109 |

+-------+------+------+------+------+-------+

5 rows in set (0.00 sec)

3.  利用SUM(IF()) 生成列,直接生成结果不再利用子查询

mysql> select ifnull(c1,'total'),

-> sum(if(c2='B1',C3,0)) AS B1,

-> sum(if(c2='B2',C3,0)) AS B2,

-> sum(if(c2='B3',C3,0)) AS B3,

-> sum(if(c2='B4',C3,0)) AS B4,SUM(C3) AS TOTAL

-> from tx

-> group by C1 with rollup ;

+--------------------+------+------+------+------+-------+

| ifnull(c1,'total') | B1   | B2   | B3   | B4   | TOTAL |

+--------------------+------+------+------+------+-------+

| A1                 |    9 |    2 |    1 |   11 |    23 |

| A2                 |    7 |    9 |    8 |    7 |    31 |

| A3                 |    4 |    8 |    8 |    8 |    28 |

| A4                 |    2 |    5 |    6 |   14 |    27 |

| total              |   22 |   24 |   23 |   40 |   109 |

+--------------------+------+------+------+------+-------+

5 rows in set (0.00 sec)

4. 动态，适用于列不确定情况，

mysql> SET @EE='';

mysql> SELECT @EE:=CONCAT(@EE,'SUM(IF(C2=\'',C2,'\'',',C3,0)) AS ',C2,',') FROM (SELECT DISTINCT C2 FROM TX) A;

mysql> SET @QQ=CONCAT('SELECT ifnull(c1,\'total\'),',LEFT(@EE,LENGTH(@EE)-1),' ,SUM(C3) AS TOTAL FROM TX GROUP BY C1 WITH ROLLUP');

Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt2 FROM @QQ;

Query OK, 0 rows affected (0.00 sec)

Statement prepared

mysql> EXECUTE stmt2;

+--------------------+------+------+------+------+-------+

| ifnull(c1,'total') | B1   | B2   | B3   | B4   | TOTAL |

+--------------------+------+------+------+------+-------+

| A1                 |    9 |    2 |    1 |   11 |    23 |

| A2                 |    7 |    9 |    8 |    7 |    31 |

| A3                 |    4 |    8 |    8 |    8 |    28 |

| A4                 |    2 |    5 |    6 |   14 |    27 |

| total              |   22 |   24 |   23 |   40 |   109 |

+--------------------+------+------+------+------+-------+

5 rows in set (0.00 sec)

5.使用case，

SELECT ifnull(c1,'total')`,

MAX(

CASE

WHEN c2= 'B1' THEN C3

END

) AS B1,

MAX(

CASE

WHEN c2= 'B2' THEN C3

END

) AS B2,

MAX(

CASE

WHEN c2= 'B3' THEN C3

END

) AS B3,

MAX(

CASE

WHEN c2= 'B4' THEN C3

END

) AS B4

FROM tx GROUP BY c1

其实数据库中也可以用 CASE WHEN / DECODE 代替 IF

参考博客：

http://blog.chinaunix.net/uid-7692530-id-2567582.html

相关文章：

  • Oracle创建主键时处理重复数据的程序
  • mysql engine用哪个_mysql各个engine之间的区别
  • 为什么要转mysql_资深程序员剖白：我为何要从MySql转向图形数据库
  • 近代自然科学为啥未诞生在中国----中国文化的欠缺
  • 一个可以独立运行的java应用程序_在Ubuntu上将Java应用程序作为服务运行
  • Commons-net FTPClient completePendingCommand()经常使程序死掉的原因分析以及解决方式
  • mysql数据的导出与导入_浅析MySQL数据的导出与导入知识点
  • Nebula3渲染层: Graphics
  • go分析和kegg分析_一些GO及KEGG分析的知识
  • iPhone对OpenGL ES的支持程度！
  • XACT与X3DAudio整合的问题
  • mysql的分离搭建_MySQL 读写分离环境搭建
  • Thanksgiving!——2008博文视点的光荣归于支持我们的读者、专家们
  • ai文字变成路径_新手必备！AI常用快捷键和一些小技巧
  • 基于wince的网络音视频通信(简单易明版)
  • “寒冬”下的金三银四跳槽季来了，帮你客观分析一下局面
  • Android开发 - 掌握ConstraintLayout（四）创建基本约束
  • axios 和 cookie 的那些事
  • C++11: atomic 头文件
  • canvas 绘制双线技巧
  • github指令
  • Git的一些常用操作
  • Java 23种设计模式 之单例模式 7种实现方式
  • Java，console输出实时的转向GUI textbox
  • Joomla 2.x, 3.x useful code cheatsheet
  • JS函数式编程 数组部分风格 ES6版
  • Js基础——数据类型之Null和Undefined
  • leetcode378. Kth Smallest Element in a Sorted Matrix
  • Python连接Oracle
  • 今年的LC3大会没了？
  • 紧急通知：《观止-微软》请在经管柜购买！
  • 模仿 Go Sort 排序接口实现的自定义排序
  • 判断客户端类型，Android，iOS，PC
  • 异步
  • 在Unity中实现一个简单的消息管理器
  • AI算硅基生命吗，为什么？
  • 进程与线程（三）——进程/线程间通信
  • ​创新驱动，边缘计算领袖：亚马逊云科技海外服务器服务再进化
  • # include “ “ 和 # include ＜ ＞两者的区别
  • #{}和${}的区别是什么 -- java面试
  • #if 1...#endif
  • #我与Java虚拟机的故事#连载13：有这本书就够了
  • （20）目标检测算法之YOLOv5计算预选框、详解anchor计算
  • (vue)el-checkbox 实现展示区分 label 和 value（展示值与选中获取值需不同）
  • （二）c52学习之旅-简单了解单片机
  • (附源码)springboot课程在线考试系统 毕业设计 655127
  • （附源码）ssm高校社团管理系统 毕业设计 234162
  • （力扣记录）1448. 统计二叉树中好节点的数目
  • （企业 / 公司项目）前端使用pingyin-pro将汉字转成拼音
  • （亲测有效）解决windows11无法使用1500000波特率的问题
  • （十） 初识 Docker file
  • （十）【Jmeter】线程（Threads(Users)）之jp@gc - Stepping Thread Group (deprecated)
  • (转) Android中ViewStub组件使用
  • （转）es进行聚合操作时提示Fielddata is disabled on text fields by default
  • * CIL library *(* CIL module *) : error LNK2005: _DllMain@12 already defined in mfcs120u.lib(dllmodu