MySQL-EXISTS-UPDATE-判断一个表中的数据满足某个条件时更新另一个表-count or null参数-if

示例1：
判断一个表中的数据满足某个条件时（GROUP BY后count自身不同字段状态数量判断）更新另一个表-exists-如果有明显的关联字段，应该可以使用 inner join 来连表
update edu_train_user set whole_online_course_status = 1
WHERE id = #{id} and
exists (SELECT count(course_status = 2 or null) as completedCount, count(1) as total from edu_user_online_course_status
where container_id = #{containerId} and implement_id = #{implementId} and train_level = #{trainLevel}
<if test="userId != null and userId != ''">
and user_id = #{userId}
</if>
and is_delete = 0
GROUP BY user_id HAVING completedCount = total)


上面sql每次都是更新一条记录，继续优化，支持全部更新更高维度数据，也可以支持指定user_id列表

explain update edu_train_user u set whole_online_course_status = 1
WHERE container_id = "BaoXianFuWu" and implement_id = 15 and train_level = 1 and is_delete = 0 and user_id in ("admin_51","admin_52") AND
EXISTS (SELECT count(course_status = 2 or null) as completedCount, count(1) as total from edu_user_online_course_status
where u.container_id = container_id and u.implement_id = implement_id and u.train_level = train_level and is_delete = 0 and u.user_id = user_id -- 这个一定到带上，用于关联数据
GROUP BY user_id HAVING completedCount = total)

上面这个u.container_id = container_id and u.implement_id = implement_id and u.train_level = train_level条件会导致expire的结果可能有问题：
SUBQUERY: Using where; Using temporary; Using filesort

所以可以直接改成具体值，但是要保留 and u.user_id = user_id

explain update edu_train_user u set whole_online_course_status = 1
WHERE container_id = "BaoXianFuWu" and implement_id = 15 and train_level = 1 and is_delete = 0 and user_id in ("admin_51","admin_52") AND
EXISTS (SELECT count(course_status = 2 or null) as completedCount, count(1) as total from edu_user_online_course_status
where container_id = "BaoXianFuWu" and implement_id = 15 and train_level = 1 and is_delete = 0 and u.user_id = user_id -- 这个一定到带上，用于关联数据
GROUP BY user_id HAVING completedCount = total)

再次检查，edu_user_online_course_status表的变成了 Using index condition; Using where

特别提示count中的 or null 非常重要，不然这个course_status = 2的条件就不生效，变成total了。

补充一个场景，如果edu_user_online_course_status表中无数据，也设置whole_online_course_status = 1，条件改成
AND (
EXISTS (SELECT count(exam_status = 2 or null) as completedCount, count(1) as total from edu_user_offline_exam_status
where container_id ="BaoXianFuWu" and implement_id = 15 and train_level = 1 and is_delete = 0 and u.user_id = user_id GROUP BY user_id HAVING completedCount = total)
OR NOT EXISTS (SELECT 1 from edu_user_offline_exam_status
where container_id ="BaoXianFuWu" and implement_id = 15 and train_level = 1 and is_delete = 0 and u.user_id = user_id)
)


特别注意：上面的数据只能把值改成1，不能改成0，修改如下
UPDATE edu_train_user u
SET whole_online_course_status=IF ((EXISTS (
SELECT count(course_status=2 OR NULL) AS completedCount,count(1) AS total FROM edu_user_online_course_status WHERE container_id='jiangsuyanghejiuchan' AND implement_id=4 AND train_level=0 AND is_delete=0 AND u.user_id=user_id GROUP BY user_id HAVING completedCount=total) OR NOT EXISTS (
SELECT 1 FROM edu_user_online_course_status WHERE container_id='jiangsuyanghejiuchan' AND implement_id=4 AND train_level=0 AND is_delete=0 AND u.user_id=user_id)),1,0) WHERE container_id='jiangsuyanghejiuchan' AND implement_id=4 AND train_level=0 AND is_delete=0 AND user_id IN ('u1000001224781318')

示例2：
存在A表，B表，如果B表没有关联数据，就更新A表中数据

-- 得到当前容器所有要修改的数据
select d.container_id, d.user_id from edu_user_learning_course_record_every_day d WHERE d.is_delete = 0 and d.user_status = 0 and not exists (select 1 from edu_user_learning_analysis a WHERE d.container_id = a.container_id and d.user_id = a.user_id) and container_id = 'tianjintianyaoyaoye' group by d.container_id,d.user_id;

-- 得到当前容器、当前用户所有要修改的数据,ty108043 这条数据已经修改了
select d.container_id, d.user_id from edu_user_learning_course_record_every_day d WHERE d.is_delete = 0 and d.user_status = 0 and not exists (select 1 from edu_user_learning_analysis a WHERE d.container_id = a.container_id and d.user_id = a.user_id) and container_id = 'tianjintianyaoyaoye' and user_id = "ty108043" group by d.container_id,d.user_id;


-- 执行修改（不同的容器userId相同，edu_user_learning_analysis 有数据和没有数据）
UPDATE edu_user_learning_course_record_every_day d set d.user_status = 1 WHERE d.is_delete = 0 and d.user_status = 0 and not exists (select 1 from edu_user_learning_analysis a WHERE d.container_id = a.container_id and d.user_id = a.user_id) and d.container_id = 'tianjintianyaoyaoye' and d.user_id = "ty108043";

-- 测试 容器A(user1,user2) B(user1),
INSERT INTO `edu_user_learning_course_record_every_day`( `container_id`, `user_id`, user_name, `user_status`,learning_month,learning_day,department_name) VALUES ('A', 'user1',"user1", 0,"2023-02","2023-02-02",'');
INSERT INTO `edu_user_learning_course_record_every_day`( `container_id`, `user_id`, user_name, `user_status`,learning_month,learning_day,department_name) VALUES ('A', 'user2', 'user2', 0,"2023-01","2023-01-01",'');

INSERT INTO `edu_user_learning_analysis`( `container_id`, `user_id`,`user_status`,user_name,department_name,create_time,update_time) VALUES ('B', 'user1', 0, 'user1','',now(),now());

-- 理论上为空，where container_id = 'tianjintianyaoyaoye 一定不能少，为了过滤其他容器的相同userId
select * from edu_user_learning_analysis where container_id = 'tianjintianyaoyaoye' and user_id in (select d.user_id from edu_user_learning_course_record_every_day d WHERE d.is_delete = 0 and d.user_status = 0 and not exists (select 1 from edu_user_learning_analysis a WHERE d.container_id = a.container_id and d.user_id = a.user_id) and container_id = 'tianjintianyaoyaoye' group by d.user_id
)

select * from edu_user_learning_analysis where user_id = "15951855772"\G; 这个还要加上容器id
select container_id,user_id from edu_user_learning_course_record_every_day where user_id = "15951855772"\G; 这个还要加上容器id


示例3：
同步edu_course_learning_analysis_t的数据到edu_user_learning_course_record_every_day表

edu_course_learning_analysis_t表有course_id、user_id,属于一对多，所以用了子查询加上group by，下面的SQL性能还可以，尤其是第2次执行时，没有可命中的数据，速度更快，注意类似的场景要注意explain性能。
UPDATE edu_user_learning_course_record_every_day d
INNER JOIN (select * from edu_course_learning_analysis_t where container_id = #{containerId} GROUP BY user_id) a ON d.container_id = a.container_id
AND d.user_id = a.user_id
SET
d.user_name = a.user_name,
d.department_id = a.department_id,
d.department_name = a.department_name,
d.department_parent_id = a.department_parent_id,
d.department_parent_name = a.department_parent_name,
d.enterprise_name = a.enterprise_name,
d.post_name = a.post_name,
d.user_status = a.user_status
<!-- , d.update_time = d.update_time -->
WHERE
(
d.user_name <> a.user_name
OR d.department_id <> a.department_id
OR d.department_name <> a.department_name
OR d.department_parent_id <> a.department_parent_id
OR d.department_parent_name <> a.department_parent_name
OR d.enterprise_name <> a.enterprise_name
OR d.post_name <> a.post_name
OR d.user_status <> a.user_status
)
<!-- 这里意义不大，edu_course_learning_analysis_t是来自夜间定时任务的，所以时间都是当天的。其他实际情况下，可以加上更新时间，同步最近3天的数据。-->
and a.update_time > DATE_SUB(now(), INTERVAL 3 DAY)
and d.container_id = #{containerId}

上述类似用法：
UPDATE edu_train_user_whole_pass_status s
INNER JOIN (
SELECT
container_id,
implement_id,
user_id,
count( 1 ) AS train_level_count_sum
FROM
edu_train_user
WHERE
container_id = "BaoXianFuWu"
AND implement_id = 15
AND is_delete = 0
and pass_status = 1
and user_id in("admin_52")
GROUP BY
user_id
) u ON u.container_id = s.container_id
AND u.implement_id = s.implement_id
AND u.user_id = s.user_id
Set train_pass_status = if(s.train_level_count = u.train_level_count_sum,1,0)
WHERE
s.container_id = "BaoXianFuWu"
AND s.implement_id = 15
AND s.is_delete = 0
AND s.user_id in("admin_52")

特别注意：上面的数据只能把值改成1，不能改成0，修改如下（关联的表为空时）
UPDATE edu_train_user_whole_pass_status s
LEFT JOIN (
SELECT
container_id,
implement_id,
user_id,
count( 1 ) AS train_level_count_sum
FROM
edu_train_user
WHERE
container_id = 'jiangsuyanghejiuchan'
AND implement_id = 4
AND is_delete = 0
AND pass_status = 1
AND user_id IN ( 'u1000001224781318' )
GROUP BY
user_id
) u ON u.container_id = s.container_id
AND u.implement_id = s.implement_id
AND u.user_id = s.user_id
SET train_pass_status =
IF
( u.train_level_count_sum IS NOT NULL AND s.train_level_count = u.train_level_count_sum, 1, 0 )
WHERE
s.container_id = 'jiangsuyanghejiuchan'
AND s.implement_id = 4
AND s.is_delete = 0
AND s.user_id IN ( 'u1000001224781318' )


示例4：

入参 container_id = "BaoXianFuWu" and implement_id = 15 and train_level = 1

select * from edu_online_course where container_id = "BaoXianFuWu" and implement_id = 15 and train_level = 1 and is_delete = 0 ;
select * from edu_train_user where container_id = "BaoXianFuWu" and implement_id = 15 and train_level = 1 and is_delete = 0 ;
select * from edu_train_user_learning_course_record_t where container_id = "BaoXianFuWu" and implement_id = 15 and train_level = 1 and course_id in (5,6,8,9) and user_id in ("admin_51","admin_52")
select * from edu_user_online_course_status where container_id = "BaoXianFuWu" and course_id in (5,6,8,9) and user_id in("admin_51","admin_52") and implement_id = 15 and train_level = 1 and is_delete = 0 ;

都去inner join
EXPLAIN UPDATE edu_user_online_course_status s
INNER JOIN edu_train_user_learning_course_record_t t on s.container_id = t.container_id and s.implement_id = t.implement_id and s.train_level = t.train_level and s.user_id = t.user_id and s.course_id = t.course_id
INNER JOIN edu_online_course c on c.container_id = s.container_id and c.implement_id = s.implement_id and c.train_level = s.train_level and c.is_delete = s.is_delete
INNER JOIN edu_train_user u on u.container_id = s.container_id and u.implement_id = s.implement_id and u.train_level = s.train_level and u.is_delete = s.is_delete
set s.course_status = if(t.learning_progress = 100,2,1)
WHERE c.container_id = "BaoXianFuWu" and c.implement_id = 15 and c.train_level = 1 and s.is_delete = 0 and s.course_status <> 2;

user_id(edu_train_user) 和 course_id(edu_online_course) 子查询
EXPLAIN UPDATE edu_user_online_course_status s
INNER join edu_train_user_learning_course_record_t t on s.container_id = t.container_id and s.implement_id = t.implement_id and s.train_level = t.train_level and s.user_id = t.user_id and s.course_id = t.course_id
set s.course_status = if(t.learning_progress = 100,2,1)
WHERE s.user_id in (select DISTINCT user_id from edu_train_user where container_id = s.container_id and implement_id = s.implement_id and train_level = s.train_level and is_delete = s.is_delete )
and s.course_id in (SELECT DISTINCT course_id from edu_online_course where container_id = s.container_id and implement_id = s.implement_id and train_level = s.train_level and is_delete = s.is_delete )
and s.container_id = "BaoXianFuWu" and s.implement_id = 15 and s.train_level = 1 and s.course_status <> 2 and s.is_delete = 0 ;
and s.course_status <> 2 为了避免重复查询

EXPLAIN结果：
四个表都有 Using where
edu_train_user、edu_online_course 多了
EXPLAIN FirstMatch(s) FirstMatch(edu_train_user)

-- 如果数据控制的比较严格的话，edu_train_user_learning_course_record_t，edu_user_online_course_status 直接根据这两个表数据查询就行

补充说明：为了避开重复扫描，需要更新的字段也加在where条件上,比如user_status。
UPDATE edu_user_learning_course_record_every_day SET user_status = #{userStatus}
WHERE container_id = #{containerId} AND user_id = #{userId} AND is_delete = 0 and user_status <> #{userStatus}

示例5：
根据当前表当前行的字段数据判断，更新其他字段状态，if(boolean条件,true时值,false时值)
update edu_train_user set pass_status = if(whole_online_course_status = 1 and whole_offline_course_status = 1,1,0) where container_id = "BaoXianFuWu" and implement_id = 15 and train_level = 1 and is_delete = 0 and user_id in("admin_51","admin_52");