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

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

回答1:

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 , 

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.