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

Question

我想使用 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条件？

Answer 1

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;

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