sql中OR AND 同时使用要注意的问题

问题描述:

       现有表数据如下

 

      

       业务需求是查询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只对左右两边的判断条件做处理。代码的细节很重要。