mysql - 如何在mysql触发器中设置值

Question

这是 mysql 查询，我想将 avg 设置为变量，而 mb.HELP 有时会变为 null，我想关闭此刻度。如果为空则除以 3，如果不为空则除以 4。也按此值将值设置到其他列，但查询得到语法错误；

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF mb.Dynamic_Help IS NULL THEN SET @newavg := (mb.Dynamic_Delivery_Sp' at line 6

查询；

CREATE TRIGGER computeColor
AFTER INSERT ON merchant_rates
FOR EACH ROW

   UPDATE merchant_branches as mb
    IF mb.Dynamic_Help  IS NULL THEN
        SET @newavg := (mb.Dynamic_Delivery_Speed + mb.Dynamic_Flavor + mb.Dynamic_Service)/3
     ELSE 
        SET @newavg := (mb.Dynamic_Delivery_Speed + mb.Dynamic_Flavor + mb.Dynamic_Service+mb.Dynamic_Help)/4
    END IF;


CASE
    WHEN @newavg >= 9   THEN  mb.Dynamic_Rank_Color = "#FFD300"
   WHEN @newavg >= 8,5  AND @newavg < 9     THEN  mb.Dynamic_Rank_Color = "#FFD300"
   WHEN @newavg >= 8    AND @newavg < 8,5   THEN  mb.Dynamic_Rank_Color = "#FFD320"
   WHEN @newavg >= 7,5  AND @newavg < 8     THEN  mb.Dynamic_Rank_Color = "#FAD300"
   WHEN @newavg >= 7    AND @newavg < 7,5   THEN  mb.Dynamic_Rank_Color = "#CCD300"
   WHEN @newavg >= 6,5  AND @newavg < 7     THEN  mb.Dynamic_Rank_Color = "#FED300"
END
    WHERE mb.id = NEW.Branch_ID

Answer 1

您可以操纵更新子句以 -

UPDATE merchant_branches as mb
    SET mb.Dynamic_Rank_Color = CASE 
                                WHEN mb.Dynamic_Help IS NOT NULL
                                THEN
                                    CASE 
                                    WHEN (mb.Dynamic_Delivery_Speed + mb.Dynamic_Flavor + mb.Dynamic_Service+mb.Dynamic_Help)/4 >= 9 THEN "#FFD300"
                                    WHEN (mb.Dynamic_Delivery_Speed + mb.Dynamic_Flavor + mb.Dynamic_Service+mb.Dynamic_Help)/4 BETWEEN 9 AND 8.5 THEN "#FFD300"
                                    WHEN (mb.Dynamic_Delivery_Speed + mb.Dynamic_Flavor + mb.Dynamic_Service+mb.Dynamic_Help)/4 BETWEEN 8.5 AND 8 THEN "#FFD300"
                                    WHEN (mb.Dynamic_Delivery_Speed + mb.Dynamic_Flavor + mb.Dynamic_Service+mb.Dynamic_Help)/4 BETWEEN 8 AND 7.5 THEN "#FAD320"
                                    WHEN (mb.Dynamic_Delivery_Speed + mb.Dynamic_Flavor + mb.Dynamic_Service+mb.Dynamic_Help)/4 BETWEEN 7.5 AND 7 THEN "#CCD300"
                                    WHEN (mb.Dynamic_Delivery_Speed + mb.Dynamic_Flavor + mb.Dynamic_Service+mb.Dynamic_Help)/4 BETWEEN 7 AND 6.5 THEN "#FED300"
                                    END
                                WHEN mb.Dynamic_Help IS NULL
                                THEN
                                    CASE 
                                    WHEN (mb.Dynamic_Delivery_Speed + mb.Dynamic_Flavor + mb.Dynamic_Service)/3 >= 9 THEN "#FFD300"
                                    WHEN (mb.Dynamic_Delivery_Speed + mb.Dynamic_Flavor + mb.Dynamic_Service)/3 BETWEEN 9 AND 8.5 THEN "#FFD300"
                                    WHEN (mb.Dynamic_Delivery_Speed + mb.Dynamic_Flavor + mb.Dynamic_Service)/3 BETWEEN 8.5 AND 8 THEN "#FFD320"
                                    WHEN (mb.Dynamic_Delivery_Speed + mb.Dynamic_Flavor + mb.Dynamic_Service)/3 BETWEEN 8 AND 7.5 THEN "#FAD300"
                                    WHEN (mb.Dynamic_Delivery_Speed + mb.Dynamic_Flavor + mb.Dynamic_Service)/3 BETWEEN 7.5 AND 7 THEN "#CCD300"
                                    WHEN (mb.Dynamic_Delivery_Speed + mb.Dynamic_Flavor + mb.Dynamic_Service)/3 BETWEEN 7 AND 6.5 THEN "#FED300"
                                    END
                                END
    WHERE mb.id = NEW.Branch_ID