MYSQL中的条件where子句

MYSQL中的条件where子句

本文介绍了MYSQL中的条件where子句的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个查询,如果名为OrderBy的标志为1,则它是一个日历事件,我需要检查两个日期时间字段之间的范围.而其他所有类型仅检查您正在查看的日期.我研究了if语句,并发现有很多建议使用案例的帖子,因此我尝试将其实现到我的查询中.我的查询需要条件在哪里.我知道我有语法问题,这就是为什么我在这里,希望有人指出正确的方法.

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.

谢谢您的时间

我当前的查询

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

推荐答案

只要使用足够的括号,就可以使用ANDOR进行此操作.

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

我还假设activities.OrderBy可以为null.如果不是这种情况,则可以删除null检查:

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

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

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

这篇关于MYSQL中的条件where子句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-20 04:39
查看更多