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屋!