MySQL变量无法按预期方式处理大小写表达式

问题不在于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; 

要分配给变量，并阅读他们相同的语句里面。这不被推荐。 From the manual

作为一般规则，比SET语句等，你永远不应该 值分配给一个用户变量和相同的 语句中读出的值。

放置SET声明之前，请查询：

SET @is_base := 'Yes'; 
SET @days := DATEDIFF('2018-02-26', '2018-02-22'); 
SELECT 
    @is_base, 
    @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 r 

至于修改后的查询，我想你可以简单地将派生表中的所有变量，并加入剩余的表吧：

FROM `vs_units` `u` 
INNER JOIN (
    SELECT 
     CASE 
      WHEN ('2018-02-22' <= r.enddate) and ('2018-02-26' >= r.startdate) or ('2018-02-22' <= r.enddate) and (r.startdate <= '2018-02-26') THEN 'No' 
      ELSE 'Yes' 
     END as is_base, 
     DATEDIFF('2018-02-26', '2018-02-22') AS days 
) params 
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` 

然后，您可以用params.days和params.is_base替换所有@days和@is_base。

PS：我觉得这个：

('2018-02-22' <= enddate) and ('2018-02-26' >= startdate) or ('2018-02-22' <= enddate) and (startdate <= '2018-02-26') 

可以写成：

('2018-02-22' <= enddate) and ('2018-02-26' >= startdate)