错误代码：1054“where clause”中的列“tz hour”未知

Question

试着让下面的工作，试着为时区做调整。如果我注释掉行和plan.resethour=tzHour,它会正确显示列tzHour,但似乎不能使用where子句中的值。

SELECT
     `issuer`.`tz`
    ,`account`.`id`
    , `plan`.`endDate`
    , `plan`.`resetDay`
    , `plan`.`resetHour`
    , `plan`.`type`
    , HOUR(NOW())
    ,   CASE issuer.`tz`
        WHEN 'US/Eastern' THEN HOUR(NOW())+1
        WHEN 'US/Central' THEN HOUR(NOW())
        ELSE HOUR(NOW())
    END AS tzHour

FROM
    `dvh`.`account`
    INNER JOIN `dvh`.`plan` 
        ON (`account`.`plan` = `plan`.`id`)
    INNER JOIN `dvh`.`issuer` 
        ON (`plan`.`issuer` = `issuer`.`id`)        
WHERE plan.type='UNIT' 
AND plan.startDate < NOW() 
AND plan.endDate >NOW() 
AND plan.resetDay=DAYOFWEEK(NOW())
AND plan.resetHour=tzHour
AND account.`active`=1;

Answer 1

通过将大小写移到where子句来解决

AND plan.`resetHour` = 
    CASE 
        WHEN issuer.`tz`='US/Eastern' THEN HOUR(NOW())+1
        WHEN issuer.`tz`='US/Central' THEN HOUR(NOW())
        WHEN issuer.`tz`='US/Mountain' THEN HOUR(NOW())-1
        WHEN issuer.`tz`='US/Pacific' THEN HOUR(NOW())-2
    END