如何在存储过程中查询的 WHERE 子句中应用特定于变量的条件

Question

请告诉我在下面的过程查询中做错了什么

CREATE PROCEDURE `DB`.`getReportsTotal`(customerId int(11),reportType varchar(20)) 
    BEGIN
        SELECT 
            `table1`.`column1`,
            `table2`.`column2`,
            reportType as `reportType`
        FROM table1
        LEFT JOIN table2
        ON table1.id = table2.table1_id 
        WHERE table2.customer_id = customerId 
        IF (reportType = "school")  
        AND `table1`.`column2` != "value";
END

如果reportType="学校则仅与CCD_ 1进行AND运算。column2！="值">此条件将适用。但是这个查询给出了mysql语法错误。

我也试过下面的查询

CREATE PROCEDURE `DB`.`getReportsTotal`(customerId int(11),reportType varchar(20)) 
    BEGIN
        SELECT 
            `table1`.`column1`,
            `table2`.`column2`,
            reportType as `reportType`
        FROM table1
        LEFT JOIN table2
        ON table1.id = table2.table1_id         
        IF (reportType = "school") WHERE table2.customer_id = customerId AND `table1`.`column2` != "value";
        ELSE WHERE table2.customer_id = customerId ;
END

但是得到mysql错误

请帮忙。。。

Answer 1

您可以使用以下逻辑。

如果不是学校使用"；值"+1，使得"；值"+1！="值"；始终是真正的

如果是学校，请对照列2 进行检查

    SELECT 
        `table1`.`column1`,
        `table2`.`column2`,
        reportType as `reportType`
    FROM table1
    LEFT JOIN table2
    ON table1.id = table2.table1_id 
    WHERE table2.customer_id = customerId AND 
    IF (reportType = "school",`table1`.`column2`,"value"+1)  != "value";