if-else在mysql中查询where子句

I am trying to change the where clause according to IF condition. The query fails for some reason. I would appreciate any help.

我试图根据IF条件更改where子句。查询由于某种原因失败。我将不胜感激任何帮助。

Thanks!

        SELECT
            c.id AS course,
            cr.id AS criteriaid,
            u.id AS userid,
            ue.timestart AS otimestart,
            (ue.timestart + cr.enrolperiod) AS ctimestart,
            ue.timecreated AS otimeenrolled,
            (ue.timecreated + cr.enrolperiod) AS ctimeenrolled
        FROM
            {user} u
        INNER JOIN
            {user_enrolments} ue
         ON ue.userid = u.id
        INNER JOIN
            {enrol} e
         ON e.id = ue.enrolid
        INNER JOIN
            {course} c
         ON c.id = e.courseid
        INNER JOIN
            {course_completion_criteria} cr
         ON c.id = cr.course
        LEFT JOIN
            {course_completion_crit_compl} cc
         ON cc.criteriaid = cr.id
        AND cc.userid = u.id
        WHERE
            cr.criteriatype = 5
        AND c.enablecompletion = 1
        AND cc.id IS NULL
        AND             
        (
        IF (ue.timeextension IS NULL)
                (ue.timestart > 0 AND ue.timestart + cr.enrolperiod < ?
          OR ue.timecreated > 0 AND ue.timecreated + cr.enrolperiod < ?)
        ELSE 
                (ue.timestart > 0 AND ue.timestart + ue.timextension < ?
          OR ue.timecreated > 0 AND ue.timecreated + ue.timeextension < ?)
        )

3 个解决方案

#1

The syntax of an IF expression is:

IF表达式的语法是:

IF(<cond-expr>, <then-expr>, <else-expr>)

There's no ELSE keyword in the syntax, you just separate the two resulting expressions with a comma. So the correct syntax of your expression should be:

语法中没有ELSE关键字,您只需用逗号分隔两个结果表达式。所以表达式的正确语法应该是:

AND IF(ue.timeextension IS NULL,
       (ue.timestart > 0 AND ue.timestart + cr.enrolperiod < ?)
         OR (ue.timecreated > 0 AND ue.timecreated + cr.enrolperiod < ?),
       (ue.timestart > 0 AND ue.timestart + ue.timextension < ?)
         OR (ue.timecreated > 0 AND ue.timecreated + ue.timeextension < ?))

Another way you could write this is:

你可以写这个的另一种方法是:

AND ((ue.timestart > 0 AND ue.timestart + IFNULL(ue.timeextesion, cr.enrolperiod) < ?)
     OR (ue.timecreated > 0 AND ue.timecreated + IFNULL(ue.timeextesion, cr.enrolperiod)) < ?)

#2

Depending on what you want to accomplish, the correct where clause might be:

根据您要完成的内容,正确的where子句可能是:

...
where ...
and (if(ue.timeextension IS NULL,
        ue.timestart > 0 AND ue.timestart + cr.enrolperiod < ?
            OR ue.timecreated > 0 AND ue.timecreated + cr.enrolperiod < ?,
        ue.timestart > 0 AND ue.timestart + ue.timextension < ?
            OR ue.timecreated > 0 AND ue.timecreated + ue.timeextension < ?);

#3

I think you may try last section in the where i.e.

我想你可以试试最后一节在哪里,即

   AND             
    (
    IF (ue.timeextension IS NULL)
            (ue.timestart > 0 AND ue.timestart + cr.enrolperiod < ?
      OR ue.timecreated > 0 AND ue.timecreated + cr.enrolperiod < ?)
    ELSE 
            (ue.timestart > 0 AND ue.timestart + ue.timextension < ?
      OR ue.timecreated > 0 AND ue.timecreated + ue.timeextension < ?)
    )

   AND             
    (
      (ue.timestart > 0 AND ue.timestart + 
             IFNULL(ue.timeextension , cr.enrolperiod )  < ? )
     OR (ue.timecreated > 0 AND ue.timecreated + 
             IFNULL (ue.timeextension, cr.enrolperiod) < ? )
    )

#1