MYSQL where 子句中的条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18451207/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Conditional in MYSQL where clause
提问by user2719164
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.
我有一个查询，如果名为 OrderBy 的标志为 1，则它是一个日历事件，我需要检查两个日期时间字段之间的范围。而所有其他类型我们只检查您正在查看的那一天。我研究了 if 语句，并看到许多帖子建议使用案例，因此我尝试将其实施到我的查询中。我的查询需要 where 中的条件。我知道我有语法问题，这就是为什么我在这里希望有人能指出正确的方法来做到这一点。
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
回答by Ike Walker
You can do this with ANDand ORas long as you use sufficient parentheses.
你可以做到这一点AND，并OR只要你用足够的括号内。
Also I'm assuming that activities.OrderBycan be null. If that's not the case you can remove the null check:
另外我假设activities.OrderBy可以为空。如果不是这种情况，您可以删除空检查：
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 CASEstatement using END, like this:
或者，如果您仍然想使用CASE，您只需要使用 关闭您的CASE语句END，如下所示：
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
