mysql where子条件,MYSQL中的条件where子句

I have a query that if a flag called OrderBy is 1 then it is a calendar event and I need to check for a range between two date time fields. Whereas all other types we just check for the day you are viewing. I research if statements and saw many posts where it was suggested a case be used so I tried to implement it into my query. My query needs the condition in the where. I know I have syntax issues and that is why I am here in the hopes that someone could point out the correct way to do this.

Thank you for your time

My query currently

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'

order BY activitytypes.orderby,

activities.starttime

解决方案

You can do this with AND and OR as long as you use sufficient parentheses.

Also I'm assuming that activities.OrderBy can be null. If that's not the case you can remove the null check:

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

Alternatively, if you still want to use CASE, you just need to close your CASE statement using END, like this:

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