【项目中MySQL统计列中属性出现次数场景解决，或统计一列中不同值出现次数的场景】

最新推荐文章于 2023-10-27 10:45:24 发布

漫慢丶

最新推荐文章于 2023-10-27 10:45:24 发布

阅读量804

点赞数 1

分类专栏： MySQL java常见问题文章标签： mysql sql

本文链接：https://blog.csdn.net/qq_43660506/article/details/126958686

版权

java常见问题同时被 2 个专栏收录

14 篇文章 1 订阅

订阅专栏

MySQL

4 篇文章 0 订阅

订阅专栏

MySQL中分组某列，并且统计每行数据对应每个属性值的数量

功能需求
- 代码实现

功能需求

在A表中，包含了作业单位，和作业类型两个属性：
其中单位单位不固定，动态添加；
作业类型固定：12种；
需求：
统计出每个单位对应得12种作业类型的记录条数。
如图：（因涉及私密，所以模糊处理。）
在这里插入图片描述

代码实现

方法1、使用 SUM的方式实现

思路：1.先对作业单位进行分组，GROUP BY。
2、其次对作业类型每一种进行查询，并使用SUM统计数量，使用IF充当过滤条件

SELECT Applicant,
       SUM(OperationTypeName='动火作业') AS A1,
       SUM(OperationTypeName='动土作业') AS A2,
       SUM(OperationTypeName='临时用电') AS A3,
       SUM(OperationTypeName='高处作业') AS A4,
       SUM(OperationTypeName='11') AS A5,
       SUM(OperationTypeName='dd') AS A6,
       SUM(OperationTypeName='ee') AS A7,
       SUM(OperationTypeName='ff') AS A8,
       SUM(OperationTypeName='yy') AS A9

FROM t_sep_zyxk_jc
WHERE DeleteFlg=0
GROUP BY Applicant

查询结果

类比需求图，将结果封装返回即可。
在这里插入图片描述

方法2、使用 COUNT的方式实现

思路：实现方式跟上面一样，将SUM换成COUNT即可

SELECT Applicant,
       COUNT(OperationTypeName='动火作业') AS A1,
       COUNT(OperationTypeName='动土作业') AS A2,
       COUNT(OperationTypeName='临时用电') AS A3,
       COUNT(OperationTypeName='高处作业') AS A4,
       COUNT(OperationTypeName='11') AS A5,
       COUNT(OperationTypeName='dd') AS A6,
       COUNT(OperationTypeName='ee') AS A7,
       COUNT(OperationTypeName='ff') AS A8,
       COUNT(OperationTypeName='yy') AS A9

FROM t_sep_zyxk_jc
WHERE DeleteFlg=0
GROUP BY Applicant

方法3、使用SUM+IF的方式实现

SELECT Applicant,
       SUM(IF(OperationTypeName='动火作业',1,0)) AS A1,
       SUM(IF(OperationTypeName='动土作业',1,0)) AS A2,
       SUM(IF(OperationTypeName='临时用电',1,0)) AS A3,
       SUM(IF(OperationTypeName='高处作业',1,0)) AS A4,
       SUM(IF(OperationTypeName='动火作业',1,0)) AS A5,
       SUM(IF(OperationTypeName='动火作业',1,0)) AS A6,
       SUM(IF(OperationTypeName='动火作业',1,0)) AS A7,
       SUM(IF(OperationTypeName='动火作业',1,0)) AS A8,
       SUM(OperationTypeName is not null) AS '总和'

FROM t_sep_zyxk_jc
WHERE DeleteFlg=0
GROUP BY Applicant;