原创

SQL-join-记录排查SQL on条件缺少的过程

案例1：
select a.id
, a.course_hour as courseHour
, a.course_id as courseId
, a.train_level as trainLevel
, b.name as courseName
, b.status as coursePublicStatus
, b.is_delete as isDelete
, d.learning_time as learningTime
, d.learning_progress as learningProgress
, e.course_status as courseStatus
from edu_online_course a
left join edu_course b
on a.course_id = b.id
left join (select * from edu_train_user where
implement_id = 5096
and container_id = 'AnQuanShengChanGong2'
and is_delete = 0
and user_id = 'admin_51'
) c on a.implement_id = c.implement_id and a.train_level = c.train_level
left join (select * from edu_train_user_learning_course_record_t
where container_id = 'AnQuanShengChanGong2' and user_id = 'admin_51') d
on b.id = d.course_id and d.user_id = c.user_id
left join (select * from edu_user_online_course_status
where user_id = 'admin_51'
and implement_id = 5096
and container_id = 'AnQuanShengChanGong2'
and is_delete = 0
) e
on a.implement_id = e.implement_id and a.train_level = e.train_level and
c.user_id = e.user_id and a.course_id = e.course_id and a.id = e.online_course_id
where a.implement_id = 5096
and a.container_id = 'AnQuanShengChanGong2'
and a.is_delete = 0 and b.container_id = 'AnQuanShengChanGong2'
and a.train_level = #{trainLevel}
找到 相同 和 不同 的 两个表，加个on条件
-----+------------+----------+------------+--------------------------------------+--------------------+----------+--------------+------------------+--------------+
| id | courseHour | courseId | trainLevel | courseName | coursePublicStatus | isDelete | learningTime | learningProgress | courseStatus |
+-----+------------+----------+------------+--------------------------------------+--------------------+----------+--------------+------------------+--------------+
| 566 | 1.0 | 5135 | 1 | 测试课程0415-6（三级培训） | 4 | 0 | 104 | 100.00 | NULL |
| 566 | 1.0 | 5135 | 1 | 测试课程0415-6（三级培训） | 4 | 0 | 10 | 9.61 | 1 |
| 567 | 2.0 | 5135 | 2 | 测试课程0415-6（三级培训） | 4 | 0 | 104 | 100.00 | NULL |
| 567 | 2.0 | 5135 | 2 | 测试课程0415-6（三级培训） | 4 | 0 | 10 | 9.61 | NULL |
| 568 | 3.0 | 5135 | 3 | 测试课程0415-6（三级培训） | 4 | 0 | 104 | 100.00 | NULL |
| 568 | 3.0 | 5135 | 3 | 测试课程0415-6（三级培训） | 4 | 0 | 10 | 9.61 | NULL |
+-----+------------+----------+------------+--------------------------------------+--------------------+----------+--------------+------------------+--------------+



案例2：
tail -200f ./logs/xxx。log | grep http
前端控制台 单独再 请求下接口 ，拿到sql日志
select
a.id,
a.user_id as userId,
a.course_status as courseStatus,
b.learning_progress as learningProgress,
a.face_recognition_result AS faceRecognitionResult,
b.learning_time as learningTime,
b.update_time as updateTime,
oc.course_hour as courseHour,
oc.face_recognition_times as faceRecognitionTimes,
c.name as courseName
from
edu_user_online_course_status a
left join edu_train_user_learning_course_record_t b on b.container_id = a.container_id
and a.course_id = b.course_id
and a.user_id = b.user_id
and b.implement_id = a.implement_id
and b.train_level = 0
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 = 5111
and oc.train_level = 0
and oc.train_level = 0
and a.online_course_id = 614
and a.container_id = 'AnQuanShengChanGong2'
and a.is_delete = 0
limit 10
+-------+-------------------+--------------+------------------+-----------------------+--------------+---------------------+------------+----------------------+--------------------+
| id | userId | courseStatus | learningProgress | faceRecognitionResult | learningTime | updateTime | courseHour | faceRecognitionTimes | courseName |
+-------+-------------------+--------------+------------------+-----------------------+--------------+---------------------+------------+----------------------+--------------------+
| 20220 | u0000001888143965 | 2 | 100.00 | 0 | 382 | 2022-06-10 11:16:22 | 5.0 | 0 | 测试课程0609-2 |
| 20220 | u0000001888143965 | 2 | 100.00 | 0 | 382 | 2022-06-10 11:16:22 | 5.0 | 3 | 测试课程0609-2 |



select
a.id,
b.id,
oc.id,
c.id
from
edu_user_online_course_status a
left join edu_train_user_learning_course_record_t b on b.container_id = a.container_id
and a.course_id = b.course_id
and a.user_id = b.user_id
and b.implement_id = a.implement_id
and b.train_level = 0
left join edu_online_course oc on oc.course_id = a.course_id 【最后修复的 and a.is_delete = oc.is_delete 】
AND oc.implement_id = a.implement_id
left join edu_course c on c.id = oc.course_id
where
a.implement_id = 5111
and oc.train_level = 0
and oc.train_level = 0
and a.online_course_id = 614
and a.container_id = 'AnQuanShengChanGong2'
and a.is_delete = 0
limit 10

+-------+-------+------+------+
| id | id | id | id |
+-------+-------+------+------+
| 20220 | 14965 | 611 | 5157 |
| 20220 | 14965 | 614 | 5157 |
+-------+-------+------+------+

这条oc表的不同，基本就表示 oc 的on 条件需要增加

select * from edu_online_course oc where id in (611,614)

+-----+----------------------+--------------+-------------+-----------+-------------+----------------+--------------+------------+------------+---------------------+---------------------+-----------+------------------------+
| id | container_id | implement_id | train_level | course_id | course_hour | attachment_ids | authority_id | creater_id | updater_id | create_time | update_time | is_delete | face_recognition_times |
+-----+----------------------+--------------+-------------+-----------+-------------+----------------+--------------+------------+------------+---------------------+---------------------+-----------+------------------------+
| 611 | AnQuanShengChanGong2 | 5111 | 0 | 5157 | 5.0 | NULL | -1 | admin_51 | admin_51 | 2022-06-10 10:31:43 | 2022-06-10 10:34:52 | 1 | 0 |
| 614 | AnQuanShengChanGong2 | 5111 | 0 | 5157 | 5.0 | NULL | -1 | admin_51 | admin_51 | 2022-06-10 10:35:02 | 2022-06-10 10:35:02 | 0 | 3 |
+-----+----------------------+--------------+-------------+-----------+-------------+----------------+--------------+------------+------------+---------------------+---------------------+-----------+------------------------+

未过滤删除的数据。

寻找缺少关联条件：
select a.id
, a.course_hour as courseHour
, a.course_id as courseId
, a.train_level as trainLevel
, b.name as courseName
, b.status as coursePublicStatus
, b.is_delete as isDelete
, d.learning_time as learningTime
, d.learning_progress as learningProgress
, e.course_status as courseStatus
from edu_online_course a
left join edu_course b
on a.course_id = b.id
left join (select * from edu_train_user where
implement_id = 5164
and container_id = "AnQuanShengChanGong2"
and is_delete = 0
and user_id = 'admin_51'
) c on a.implement_id = c.implement_id and a.train_level = c.train_level
left join (select * from edu_train_user_learning_course_record_t
where container_id = "AnQuanShengChanGong2" and user_id = 'admin_51') d
on b.id = d.course_id and d.user_id = c.user_id and d.train_level = a.train_level
left join (select * from edu_user_online_course_status
where user_id = 'admin_51'
and implement_id = 5164
and container_id = "AnQuanShengChanGong2"
and is_delete = 0
) e
on a.implement_id = e.implement_id and a.train_level = e.train_level and
c.user_id = e.user_id and a.course_id = e.course_id and a.id = e.online_course_id
where a.implement_id = 5164
and a.container_id = "AnQuanShengChanGong2"
and a.is_delete = 0;
+-----+------------+----------+------------+--------------------------------------------+--------------------+----------+--------------+------------------+--------------+
| id | courseHour | courseId | trainLevel | courseName | coursePublicStatus | isDelete | learningTime | learningProgress | courseStatus |
+-----+------------+----------+------------+--------------------------------------------+--------------------+----------+--------------+------------------+--------------+
| 675 | 5.5 | 5109 | 1 | 测试培训课程0402-5（三级培训） | 4 | 0 | 13 | 100.00 | 2 |
| 675 | 5.5 | 5109 | 1 | 测试培训课程0402-5（三级培训） | 4 | 0 | 13 | 100.00 | 2 |
| 676 | 5.5 | 5109 | 2 | 测试培训课程0402-5（三级培训） | 4 | 0 | 13 | 100.00 | 2 |
| 677 | 5.5 | 5109 | 3 | 测试培训课程0402-5（三级培训） | 4 | 0 | 13 | 100.00 | 2 |
+-----+------------+----------+------------+--------------------------------------------+--------------------+----------+--------------+------------------+--------------+


-- 只看主表id
select a.id
, a.id
, d.id
, e.id
from edu_online_course a
left join edu_course b
on a.course_id = b.id
left join (select * from edu_train_user where
implement_id = 5164
and container_id = "AnQuanShengChanGong2"
and is_delete = 0
and user_id = 'admin_51'
) c on a.implement_id = c.implement_id and a.train_level = c.train_level
left join (select * from edu_train_user_learning_course_record_t
where container_id = "AnQuanShengChanGong2" and user_id = 'admin_51') d
on b.id = d.course_id and d.user_id = c.user_id and d.train_level = a.train_level
left join (select * from edu_user_online_course_status
where user_id = 'admin_51'
and implement_id = 5164
and container_id = "AnQuanShengChanGong2"
and is_delete = 0
) e
on a.implement_id = e.implement_id and a.train_level = e.train_level and
c.user_id = e.user_id and a.course_id = e.course_id and a.id = e.online_course_id
where a.implement_id = 5164
and a.container_id = "AnQuanShengChanGong2"
and a.is_delete = 0;
| id | id | id | id |
+-----+-----+------+-------+
| 675 | 675 | 2 | 27255 |
| 675 | 675 | 3 | 27255 |
| 676 | 676 | 4 | 27256 |
| 677 | 677 | 5 | 27257 |
+-----+-----+------+-------+

d.id 2,3

mysql> select * from edu_train_user_learning_course_record_t
-> where container_id = "AnQuanShengChanGong2" and user_id = 'admin_51' and id in (2,3);
+-------+----------------------+-----------+----------+--------------+-------------+-------------------+---------------+---------------+-------------------+---------------------+---------------------+----------------------+
| id | container_id | course_id | user_id | implement_id | train_level | learning_duration | learning_page | learning_time | learning_progress | create_time | update_time | actual_learning_time |
+-------+----------------------+-----------+----------+--------------+-------------+-------------------+---------------+---------------+-------------------+---------------------+---------------------+----------------------+
| 2 | AnQuanShengChanGong2 | 5109 | admin_51 | 5163 | 1 | 0 | 13 | 13 | 100.00 | 2022-06-13 18:38:42 | 2022-06-13 18:38:44 | 0 |
| 3 | AnQuanShengChanGong2 | 5109 | admin_51 | 5164 | 1 | 0 | 13 | 13 | 100.00 | 2022-06-13 18:45:16 | 2022-06-13 18:45:24 | 10 |
+-------+----------------------+-----------+----------+--------------+-------------+-------------------+---------------+---------------+-------------------+---------------------+-----
再看看区别是是啥。就是 and d.implement_id = a.implement_id ，即 on b.id = d.course_id and d.user_id = c.user_id and d.train_level = a.train_level and d.implement_id = a.implement_id 。

正文到此结束
所属分类：数据库
  • 本文标签： MySQL
  • 本文链接： https://code.jiangjiesheng.cn/article/314
  • 版权声明： 本文由小江同学原创发布，转载请先联系本站长，谢谢。
热门推荐
相关文章
说给你听

本文目录
    标签云
    Linux Java Spring Spring Boot 其他 MyBatis MySQL 前端 微信小程序 达梦数据库 PHP Aspose.Words ssh CSS vuejs 去哪儿网 小程序 安卓 Android jmeter 测试 数据库设计开发规范 JSP sharding-jdbc JavaScript ElasticSearch Kibana Jenkins mui 系统调优 后端 运维 C# AndriodStudio 顺风车 拼车 南京浦口到安庆桐城 SqlServer redis Jedis Druid 数据库 全栈开发 调优 沟通
    近期评论
    网站信息
    • 文章总数：330 篇
    • 标签总数：45 个
    • 分类总数：17 个
    • 留言数量：12 条
    • 在线人数：1人