原创

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       |+-------+------------+--------------+------------------+--------------+------------+------------+--------------------------+

正文到此结束

所属分类：数据库

本文标签： MySQL
本文链接： https://code.jiangjiesheng.cn/article/278
版权声明： 本文由小江同学原创发布，转载请先联系本站长，谢谢。

MySQL-代码-警惕业务表没数据导致关联基础表失败-on条件不精确导致数据重复

热门推荐

相关文章

说给你听

本文目录

标签云

近期评论

网站信息