mysql where子条件_有条件的MYSQL where子句

只要使用足够的括号,就可以使用AND和OR执行此操作.

此外,我假设的活动.OrderBy可以为null.如果不是这种情况,您可以删除空检查：

SELECT activities.*,

activitytypes.orderby

FROM activities

LEFT OUTER JOIN activitytypes

ON activities.typeid = activitytypes.typeid

WHERE activities.userid = 86

AND activities.typeid NOT IN ( 5, 10, 11, 12, 19 )

AND ( ( activities.orderby = 1

AND activities.starttime >= '2013-08-26 04:00:00'

AND activities.endtime <= '2013-08-27 04:00:00' )

OR ( ( activities.orderby IS NULL

OR activities.orderby != 1 )

AND activities.activitydate = '2013-08-26' ) )

ORDER BY activitytypes.orderby,

activities.starttime

或者,如果您仍想使用CASE,则只需使用END关闭CASE语句,如下所示：

SELECT activities.*,

activitytypes.orderby

FROM activities

LEFT OUTER JOIN activitytypes

ON activities.typeid = activitytypes.typeid

WHERE activities.userid = 86

AND activities.typeid NOT IN ( 5,

10,

11,

12,

19 )

AND (

CASE

WHEN activities.orderby = 1 THEN activities.starttime >= '2013-08-26 04:00:00'

AND activities.endtime <= '2013-08-27 04:00:00'

ELSE activities.activitydate = '2013-08-26'

end )

ORDER BY activitytypes.orderby,

activities.starttime