mysql - 带有条件的多个 WHERE 子句

Question

我看过一堆类似的 stackoverflow 问题，但我一直无法弄清楚。

                    WHERE `Table1`.`Column1` = 'criteria1'
                    AND `Table1`.`Column2` = 'criteria2'
                    AND `Table1`.`Column3` LIKE 'criteria3'
                    AND IF(EXISTS IN `Table2`, (`Table2`.`Delete` <> 2, `Table2`.`SectionId` IS NOT NULL), '')

我正在尝试执行具有五个 WHERE 子句的查询，其中最后两个子句基于该行存在于连接的 Table2 中的条件。如果条件不成立，则输入空字符串 ''。

我尝试了上述代码的变体，但它的语法不正确。我想做的事情可能吗？

我可以在 WHERE 子句中使用 IF 吗？

编辑(这是完整的查询...):

        $sql_query = "SELECT
                    `Courses`.`Id` AS CourseId,
                    IFNULL(`Courses`.`CourseName`, '') AS CourseName,
                    IF(`Courses`.`CourseNumber` > '', `Courses`.`CourseNumber`, -1) AS CourseNumber,
                    IFNULL(`Courses`.`CourseDepartment`, '') AS CourseDepartment,
                    IFNULL(`Courses`.`Notes`, '') AS CourseNotes,
                    IFNULL(`Courses`.`Year`, '') AS CourseYear,
                    IFNULL(`Courses`.`CourseType`, '') AS CourseType,
                    `Sections`.`Id` AS SectionId,
                    IFNULL(`Sections`.`SectionNumber`, '') AS SectionNumber,
                    IFNULL(`Sections`.`SectionName`, '') AS SectionName,

                    `StudentsCourses`.`CustomerId` AS CustomerId,

                    CONCAT(`Courses`.`Id`, '-', `Sections`.`Id`, '-', `StudentsCourses`.`FirstName`, `StudentsCourses`.`LastName`, '_', `StudentsCourses`.`TeacherEmail`) AS TempCustomerId,
                    IFNULL(`StudentsCourses`.`FirstName`, '') AS StudentFirstName,
                    IFNULL(`StudentsCourses`.`LastName`, '') AS StudentLastName,
                    IFNULL(`Customers`.`Email`, IFNULL(`StudentsCourses`.`StudentEmail`, '')) AS StudentEmail,
                    IFNULL(`StudentsCourses`.`TeacherFirstName`, '') AS TeacherFirstName,
                    IFNULL(`StudentsCourses`.`TeacherLastName`, '') AS TeacherLastName,
                    IF(`StudentsCourses`.`CustomerId` IS NOT NULL, `Customers`.`CustomerName`, CONCAT(`StudentsCourses`.`FirstName`, ' ', `StudentsCourses`.`LastName`)) AS FullName,
                    IF(`StudentsCourses`.`CustomerId` IS NOT NULL, CONCAT(REPLACE(`Customers`.`CustomerName`, ' ', ''), '_', `Customers`.`Email`), CONCAT(`StudentsCourses`.`FirstName`, `StudentsCourses`.`LastName`, '_', `StudentsCourses`.`TeacherEmail`)) AS NameKey,
                    `Customers`.`UserRoleId` AS UserRoleId,
                    `Customers`.`MagentoId` AS MagentoId,
                    `StudentsCourses`.`StudentId` AS StudentId,
                    `SiteProfile`.`StudentIdField` AS StudentIdField,
                    `SiteProfile`.`SchoolEmailDomain` AS SchoolEmailDomain,
                    `StudentsCourses`.`Id` AS StudentsCoursesId,
                    IFNULL(`Orders`.`Id`, '') AS OrderId
                FROM `Courses`
                    LEFT JOIN `StudentsCourses` ON `Courses`.`Id` = `StudentsCourses`.`CourseId`
                    LEFT JOIN `BooksCourses` ON `Courses`.`Id` = `BooksCourses`.`CourseId`
                    LEFT JOIN `Products` ON `BooksCourses`.`ISBN` = `Products`.`ISBN`
                    LEFT JOIN `EbookVendors` ON `Products`.`EbookVendorId` = `EbookVendors`.`Id`
                    LEFT JOIN `Sections` ON `Sections`.`Id` = `StudentsCourses`.`SectionId`
                    LEFT JOIN `Customers` ON `StudentsCourses`.`CustomerId` = `Customers`.`Id`
                    LEFT JOIN `SiteProfile` ON `Courses`.`SchoolCode` = `SiteProfile`.`SchoolCode`
                    LEFT JOIN `Orders` ON `Customers`.`Id` = `Orders`.`CustomerId`

                    WHERE `Courses`.`SchoolCode` = '{$criteria["school_code"]}'
                    AND `Courses`.`Year` = {$criteria["year"]}
                    AND `Courses`.`CourseType` LIKE '{$criteria["term"]}'
                    AND `StudentsCourses`.`Delete` <> 2
                    AND `StudentsCourses`.`SectionId` IS NOT NULL";

Answer 1

不太清楚您到底在问什么，但我猜测像这样的条件可能就是您正在寻找的东西:

AND (StudentCourses.CourseId IS NULL 
     OR (`StudentsCourses`.`Delete` <> 2
         AND `StudentsCourses`.`SectionId` IS NOT NULL
        )
    )

编辑:最好编辑您的问题，以简单的语言解释您希望条件执行的操作，因为从您最初提供的语法中根本看不出其意图。