SQL-select出指定条件的count数量-ifnull-sum-if-ifnull(sum(if((-对指定条件进行sum求和（或count）
示例1：
SELECT ifnull(sum(if((course_status = 1), 1, 0)),0) AS finishedCourseCount, 注意写法 带条件的用 sum （是对1, 0求和）,不确定使用count行不行
ifnull(count(1),0) AS totalCourseCount, IF更灵活的用法，对指定条件进行sum求和（或count）
SELECT
DATE_FORMAT( learning_day, '%Y-%m' ) AS MONTH,
ifnull( sum( total_actual_learning_time ), 0 ) AS totalCourseTime,
ifnull( sum( IF ( ( learning_requirement = 1 ), total_actual_learning_time, 0 ) ), 0 ) AS electiveCourseTime,
ifnull( sum( IF ( ( learning_requirement = 2 ), total_actual_learning_time, 0 ) ), 0 ) AS requiredCourseTime,
ifnull( sum( IF ( ( learning_requirement = 3 ), total_actual_learning_time, 0 ) ), 0 ) AS optionalCourseTime
FROM
edu_user_learning_course_record_every_day
WHERE
container_id = "BaoXianFuWu"
AND user_id = "admin_51"
AND is_delete = 0
GROUP BY
MONTH
另外特别注意join表时，sum 或count的数量可能重复计算
示例2：
关键词：
去重 count、带条件的去重 count [结合子查询]、不去重 count、带条件的sum、
统计所有数据、按分组统计最新数据
select
-- 去重 count
count(DISTINCT d.user_id) as checkPeopleCount,
-- 带条件的去重 count [结合子查询]
(select count(DISTINCT user_id) from fire_fighting_check_task_record where sex = 1 and container_id = d.container_id and check_task_id = d.check_task_id and is_delete = 0
) as manCheckPeopleCount,
(select count(DISTINCT user_id) from fire_fighting_check_task_record where sex = 2 and container_id = d.container_id and check_task_id = d.check_task_id and is_delete = 0
) as womanCheckPeopleCount,
-- 后面几个平均数，是包括一个人多次考试的平均数，并不是取最新的数据，如果只计算每个人最新的数据，那就先结合《MySQL-group by-按字段group by分组 并 返回最新的结果》，select 主体不变化 ,看sql from 2
-- 不去重 count
ROUND(sum(UNIX_TIMESTAMP(d.end_time) - UNIX_TIMESTAMP(d.start_time) ) / count(d.user_id),2) as avgScore,
-- 带条件的sum
ROUND(sum(if(d.sex = 1,UNIX_TIMESTAMP(d.end_time) - UNIX_TIMESTAMP(d.start_time),0)) / count( (d.sex = 1) or null),2) as manAvgScore,
ROUND(sum(if(d.sex = 2,UNIX_TIMESTAMP(d.end_time) - UNIX_TIMESTAMP(d.start_time),0)) / count( (d.sex = 2) or null),2) as womanAvgScore ,
ROUND(count((d.check_result = 2) or null) * 100 / count(d.id),2) as passRate,
ROUND(count((d.check_result = 2 and d.sex = 1) or null) * 100 / count((d.sex = 1) or null),2) as manPassRate,
ROUND(count((d.check_result = 2 and d.sex = 2) or null) * 100 / count((d.sex = 2) or null),2) as womanPassRate
sql from 1：统计所有的数据（同一个人可能多条数据，注意：平均中去掉了 DISTINCT，统计了所有的数据（同一个人可能多条数据） ）
from fire_fighting_check_task_record d WHERE container_id = "wanhuajituan" and check_task_id = 11 and is_delete = 0
sql from 2：只计算每个人最新的数据(按user_id分组统计最新数据,注意 GROUP BY user_id ，指定了where check_task_id = x)
<!--
FROM (SELECT user_id, max(id) latest_pk_id
FROM fire_fighting_check_task_record where container_id = "wanhuajituan" and check_task_id = 11 and is_delete = 0
GROUP BY user_id ORDER BY id desc
) b
INNER JOIN fire_fighting_check_task_record d ON d.user_id = b.user_id and d.id = b.latest_pk_id ;
-->
热门推荐
相关文章
近期评论
-
来自： 运维-docker专题-创建镜像-操作容器-制作jdk17的docker镜像等
-
来自： 留言板
-
来自： 留言板
-
来自： js-处理网页图片宽度自适应屏幕宽度
-
来自： 留言板
