mysql - 根据自定义计算字段过滤行

我想过滤使用WHERE条件返回的行。问题是MySQL不允许我在WHERE子句中使用自定义列名称。它给出了语法错误。

SELECT jobs.*,

        CASE
            WHEN job_status = 'submitted'           THEN ((2 * 86400) - ($mktime - job_timestamp))/86400
            WHEN job_status = 'agreement_pending'   THEN ((1 * 86400) - ($mktime - job_tstamp_agpending))/86400
            WHEN job_status = 'payment_complete'    THEN ((job_duration * 86400) - ($mktime - job_tstamp_pcomplete))/86400
            ELSE 1000000
        END AS sla

FROM jobs
WHERE sla < 0

是否仍然可以过滤出WHERE sla < 0条件的行？

最佳答案

MySQL有一个特殊的扩展名，您可以在其中使用having子句来做到这一点：

SELECT jobs.*,

        CASE
            WHEN job_status = 'submitted'           THEN ((2 * 86400) - ($mktime - job_timestamp))/86400
            WHEN job_status = 'agreement_pending'   THEN ((1 * 86400) - ($mktime - job_tstamp_agpending))/86400
            WHEN job_status = 'payment_complete'    THEN ((job_duration * 86400) - ($mktime - job_tstamp_pcomplete))/86400
            ELSE 1000000
        END AS sla

FROM jobs
HAVING sla < 0;

其他数据库不支持此功能。