MySQL 变量在 case 表达式中不起作用

Question

以下是我的查询的一部分，其中变量 @days 在 CASE 表达式中不起作用，而它在以前版本的 MariaDB 中运行良好。如果我将 DATEDIFF('2018-02-26', '2018-02-22') 放在案例表达式部分的 @days 中，它就会起作用但是如果我将它分配给一个变量，为什么它不起作用呢？

SELECT @is_base := 'Yes', @days := DATEDIFF('2018-02-26', '2018-02-22') days,
(CASE 
  WHEN (@is_base = 'No' AND @days < 7) THEN r.nightly * @days
  WHEN (@is_base = 'No' AND @days >= 7 AND @days < 28) THEN (r.weekly / 7) * @days
  WHEN (@is_base = 'No' AND @days >= 28) THEN (r.monthly / 28) * @days
  ELSE u.base_rate_nightly * @days
END) AS total_price

更新 以下是完整的查询。

`SELECT `u`.`ID`, `u`.`destination`, `u`.`unit_name`, `u`.`base_rate_nightly`, `u`.`lat`, `u`.`lng`, `u`.`number_of_bedrooms`, `u`.`max_guests`, `r`.`ID` as `rate_id`, `r`.`nightly`, `r`.`weekly`, `r`.`monthly`, `i`.`filename`, `t`.`name` as `unit_type`, @days := DATEDIFF('2018-02-26', '2018-02-22') days, `u`.`base_rate_nightly` as `total_price`, 'Yes' is_base, @is_base := if(('2018-02-22' <= r.enddate) and ('2018-02-26' >= r.startdate) or ('2018-02-22' <= r.enddate)  and  (r.startdate <= '2018-02-26'), 'No', 'Yes') as is_base, CASE 
                        WHEN (@is_base = 'No' AND @days < 7) THEN r.nightly * @days
                        WHEN (@is_base = 'No' AND @days >= 7 AND @days < 28) THEN (r.weekly / 7) * @days
                        WHEN (@is_base = 'No' AND @days >= 28) THEN (r.monthly / 28) * @days
                        ELSE u.base_rate_nightly * @days
                    END AS total_price
FROM `vs_units` `u`
LEFT JOIN `vs_unit_images` `i` ON `u`.`ID` = `i`.`ID_unit`
LEFT JOIN `vs_unit_rates` `r` ON `u`.`ID` = `r`.`ID_unit` AND (('2018-02-22' <= r.enddate) and ('2018-02-26' >= r.startdate) or ('2018-02-22' <= r.enddate)  and (`r`.`startdate` <= '2018-02-26'))
LEFT JOIN `vs_unit_types_readonly` `t` ON `t`.`ID` = `u`.`ID_unit_type`
WHERE `u`.`max_guests` >= '1'
AND   (
`u`.`ID` NOT IN(SELECT DISTINCT ID_unit from vs_calendar WHERE ('2018-02-22' <= enddate) and ('2018-02-26' >= `startdate`) or ('2018-02-22' <= `enddate`)  and  (startdate <= '2018-02-26'))
 )
GROUP BY `u`.`ID`

Answer 1

问题不在于 CASE 表达式。它是在多个列中使用变量。 MySQL 不保证表达式中变量赋值的顺序。

对于您的情况，您有一个简单的解决方案。只需将逻辑移至 FROM 子句即可:

SELECT @days as days,
       (CASE WHEN @is_base = 'No' AND @days < 7 THEN r.nightly * @days
             WHEN @is_base = 'No' AND @days >= 7 AND @days < 28 THEN (r.weekly / 7) * @days
             WHEN @is_base = 'No' AND @days >= 28 THEN (r.monthly / 28) * @days
             ELSE u.base_rate_nightly * @days
        END) AS total_price
FROM (SELECT @is_base := 'Yes',
             @days := DATEDIFF('2018-02-26', '2018-02-22')
     ) params;

FROM 子句保证在 SELECT 之前被求值。

我应该注意，使用这种结构，不需要使用变量:

SELECT days,
       (CASE WHEN is_base = 'No' AND days < 7 THEN r.nightly * days
             WHEN is_base = 'No' AND days >= 7 AND days < 28 THEN (r.weekly / 7) * days
             WHEN is_base = 'No' AND days >= 28 THEN (r.monthly / 28) * days
             ELSE u.base_rate_nightly * days
        END) AS total_price
FROM (SELECT 'Yes' as is_base,
             DATEDIFF('2018-02-26', '2018-02-22') as days
     ) params;