问题描述:
现有表数据如下
业务需求是查询type=4或者type=5,且last_remind小于今日日期。
错误1:
SELECT
smco.id AS operationId,
smco.user_id AS userId,
ssu.zusercode,
smco.course_id AS courseId,
smco.type
FROM
sc_master_course_operation smco
LEFT JOIN sc_sys_user ssu ON smco.user_id = ssu.id
WHERE
smco.type = 4 or smco.type = 5
AND SUBSTR(smco.last_remind,1,10) < DATE(NOW())
查询结果:
错误2:
SELECT
smco.id AS operationId,
smco.user_id AS userId,
ssu.zusercode,
smco.course_id AS courseId,
smco.type
FROM
sc_master_course_operation smco
LEFT JOIN sc_sys_user ssu ON smco.user_id = ssu.id
WHERE
SUBSTR(smco.last_remind,1,10) < DATE(NOW())
AND smco.type = 4 or smco.type = 5
查询结果:
正确的sql:
SELECT
smco.id AS operationId,
smco.user_id AS userId,
ssu.zusercode,
smco.course_id AS courseId,
smco.type
FROM
sc_master_course_operation smco
LEFT JOIN sc_sys_user ssu ON smco.user_id = ssu.id
WHERE
SUBSTR(smco.last_remind,1,10) < DATE(NOW())
AND (smco.type = 4 or smco.type = 5)
正确的查询结果:
总结一下,思路很清晰,可惜代码表达不到位。
or只对左右两边的判断条件做处理。代码的细节很重要。