MySQL-代码-警惕业务表没数据导致关联基础表失败-on条件不精确导致数据重复
SELECT
a.id,
a.user_id AS userId,
a.course_status AS courseStatus,
b.learning_progress AS learningProgress,
b.learning_time AS learningTime,
b.update_time AS updateTime,
oc.course_hour AS courseHour,
c.NAME AS courseName
FROM
edu_user_online_course_status a
LEFT JOIN ( SELECT * FROM edu_user_learning_course_record_t WHERE container_id = 'AnQuanShengChanGong2' ) b ON a.course_id = b.course_id
AND a.user_id = b.user_id
LEFT JOIN edu_online_course oc ON oc.course_id = b.course_id
LEFT JOIN edu_course c ON c.id = oc.course_id
WHERE
a.implement_id = 4991
AND a.container_id = 'AnQuanShengChanGong2'
AND a.is_delete = 0
AND a.course_id = 4735
AND a.user_id IN ( 'admin_51', 'admin_aqwy' );
id | userId | courseStatus | learningProgress | learningTime | updateTime | courseHour | courseName |
+-------+------------+--------------+------------------+--------------+------------+------------+------------+
| 16787 | admin_51 | 0 | NULL | NULL | NULL | NULL | NULL |
| 16788 | admin_aqwy | 0 | NULL | NULL | NULL | NULL | NULL |
+-------+------------+--------------+------------------+--------------+------------+------------+------------+
-- 警惕 b表无数据，导致left join edu_online_course oc on oc.course_id = b.course_id 关联失败,进而导致oc.course_id无值，edu_course c ON c.id = oc.course_id 也就关联不到edu_course表数据，
所以要把left join edu_online_course oc on oc.course_id = b.course_id 改成 LEFT JOIN edu_online_course oc ON oc.course_id = a.course_id.
-- 另外 edu_online_course 表中 同一个courseid,会有不同实施下的数据，而在edu_course中一个course_id肯定是唯一的，所以要加上实施id的关联，
即LEFT JOIN edu_online_course oc ON oc.course_id = a.course_id and oc.implement_id = a.implement_id
【简单来说，重复数据中的不同值字段（implement_id 就是需要加在on条件上下】
SELECT
a.id,
a.user_id AS userId,
a.course_status AS courseStatus,
b.learning_progress AS learningProgress,
b.learning_time AS learningTime,
b.update_time AS updateTime,
oc.course_hour AS courseHour,
c.NAME AS courseName
FROM
edu_user_online_course_status a
LEFT JOIN ( SELECT * FROM edu_user_learning_course_record_t WHERE container_id = 'AnQuanShengChanGong2' ) b ON a.course_id = b.course_id
AND a.user_id = b.user_id
LEFT JOIN edu_online_course oc ON oc.course_id = a.course_id and oc.implement_id = a.implement_id
LEFT JOIN edu_course c ON c.id = oc.course_id
WHERE
a.implement_id = 4991
AND a.container_id = 'AnQuanShengChanGong2'
AND a.is_delete = 0
AND a.course_id = 4735
AND a.user_id IN ( 'admin_51', 'admin_aqwy' );
+-------+------------+--------------+------------------+--------------+------------+------------+--------------------------+
| id | userId | courseStatus | learningProgress | learningTime | updateTime | courseHour | courseName |
+-------+------------+--------------+------------------+--------------+------------+------------+--------------------------+
| 16787 | admin_51 | 0 | NULL | NULL | NULL | 11.0 | 测试视频课程0329-4 |
| 16788 | admin_aqwy | 0 | NULL | NULL | NULL | 11.0 | 测试视频课程0329-4 |
+-------+------------+--------------+------------------+--------------+------------+------------+--------------------------+
正文到此结束
热门推荐
相关文章
近期评论
-
来自： 运维-docker专题-创建镜像-操作容器-制作jdk17的docker镜像等
-
来自： 留言板
-
来自： 留言板
-
来自： js-处理网页图片宽度自适应屏幕宽度
-
来自： 留言板
