MySQL的情况下不工作 [英] Mysql case not working

问题描述

SELECT 
     SQL_CALC_FOUND_ROWS a.* , 
     zn.`name` AS zone_name, 
     c.`name` AS carrier_name, 
     CASE type 
       WHEN type=1 THEN 'General day' 
       ELSE 'Special date' END AS type_changed, 
    CASE week_day 
       WHEN week_day = -1 THEN 'notset' 
       WHEN week_day = 1 THEN 'monday' 
       WHEN week_day = 2 THEN 'tuesday' 
       WHEN week_day = 3 THEN 'wednesday' 
       WHEN week_day = 4 THEN 'thursday' 
       WHEN week_day = 5 THEN 'friday' 
       WHEN week_day = 6 THEN 'saturday' 
       WHEN week_day = 7 THEN 'sunday' END AS week_day_mod , 
   IF(date = '0001-01-01 00:00:0', '--', DATE(date)) AS date_mod,            IF(is_working_day = 1 ,'working day', 'day off') AS is_working_day_mod 
  FROM `ps_deliverytime_table` a 
  LEFT JOIN ps_zone AS zn ON(a.`id_zone` = zn.`id_zone`) 
  LEFT JOIN ps_carrier AS c ON(a.`id_carrier` = c.`id_carrier`) 
  WHERE 1 ORDER BY a.`id_time_table` ASC LIMIT 0,50

当星期几等于1时，它可以正常工作，但在其他情况下，则不起作用

When week day is equal 1 it works fine but in other case it do not working

推荐答案

将列名放在CASE之后时，不应使用WHEN column = value，而应使用WHEN value，因为它会自动将列与每个列进行比较WHEN子句中的值.

When you put column name after CASE, you shouldn't use WHEN column = value, just use WHEN value, because it automatically compares the column to each value in the WHEN clauses.

CASE type
    WHEN 1 THEN 'General day'
    ELSE 'Special date'
END AS type_changed,
CASE week_day 
   WHEN -1 THEN 'notset' 
   WHEN 1 THEN 'monday' 
   WHEN 2 THEN 'tuesday' 
   WHEN 3 THEN 'wednesday' 
   WHEN 4 THEN 'thursday' 
   WHEN 5 THEN 'friday' 
   WHEN 6 THEN 'saturday' 
   WHEN 7 THEN 'sunday' 
END AS week_day_mod , 

同时执行这两个操作时，您正在测试week_day = (week_day = -1)，week_day = (week_day = 1)等.它在星期一有效，因为1 = (1 = 1)等效于1 = 1，这是正确的.但在星期二，它是2 = (2 = 2)，相当于2 = 1，它是错误的.

When you do both, you're testing week_day = (week_day = -1), week_day = (week_day = 1), etc. It works on Monday because 1 = (1 = 1) is equivalent to 1 = 1, which is true; but on Tuesday, it's 2 = (2 = 2), which is equivalent to 2 = 1, which is false.

这篇关于MySQL的情况下不工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！