mysql join修改
不废话直接上语句,注意表间关系谨慎操作,一般先加个主键条件 这样影响一行 ok之后再继续操作
`UPDATE T_CLASS_CHECK_IN_DETAIL t1
LEFT JOIN T_ATTENDANCE t2 ON t1.USER_ID=t2.STUDENT_ID AND t1.COURSE_DATE=t2.COURSE_DATE
SET t1.LEAVE_TEACHER=t2.LEAVE_TEACHER,t1.LEAVE_TIME=t2.TIME_OFF,t1.LEAVE_TYPE=t2.LEAVE_TYPE,t1.REASON=t2.REASON
WHERE t2.`STATUS`='vacation' AND t1.REGIST_STATUS='LEAVE' AND t1.COURSE_DATE='2021-04-21';
mysql insert select where 操作
遇到一种情况,报名有人数限制,有一定的并发,不想用锁,用语句直接解决
我的业务场景 : 课程报名人数有限制 maxNum 达到报名人数不允许报名
上语句
语句就不解读了,能想到这种场景的应该都懂
注:这种情况会操成表锁,原因还在找
`
INSERT INTO `T_REGIST_RECORD` ( `ID`, `COURSE_ID`, `COURSE_DATE`, `STUDENT_ID`, `REGIST_TIME`, `STATUS`, `TYPE`, `TEACHER_EVALUATE_STATUS`, `STUDENT_EVALUATE_SCORE`, `CREATED`, `CREATED_BY`, `MODIFIED`, `MODIFIED_BY` ) SELECT#{record.id},
#{record.courseId},
#{record.courseDate},
#{record.studentId},
#{record.registTime},
#{record.status},
#{record.type},
#{record.teacherEvaluateStatus},
#{record.studentEvaluateScore},
#{record.created},
#{record.createdBy},
#{record.modified},
#{record.modifiedBy}
FROM
T_REGIST_RECORD_TMP
WHERE
NOT EXISTS (
SELECT
*
FROM
(
SELECT
COUNT( ID ) count
FROM
T_REGIST_RECORD
WHERE
COURSE_ID = #{record.courseId} AND `STATUS`='REGISTERED') t
WHERE
t.count >= #{maxNum})