mysql - DATEDIFF - 将 NULL 替换为 NOW()

Question

我得到了以下 SQL 查询

SELECT
    e.id,
    c.name,
    e.location,
    e.designation,
    e.time_period_from,
    e.time_period_to,
    DATEDIFF(e.time_period_to, time_period_from) AS tenure_in_days
FROM
    employment e
LEFT JOIN
    company c ON (c.id = e.company_id)
LIMIT
    0, 10

这是完美的，我有一个场景，其中 time_period_to 可以有 NULL 值，在这种情况下，我想用当前日期替换它。

这是我尝试过的。

SELECT
    e.id,
    c.name,
    e.location,
    e.designation,
    e.time_period_from,
    e.time_period_to,
    DATEDIFF(IF(ISNULL(e.time_period_to), NOW(), e.time_period_from)) AS tenure_in_days
FROM
    employment e
LEFT JOIN
    company c ON (c.id = e.company_id)
LIMIT
    0, 10

这给了我以下错误

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'DATEDIFF'

我哪里错了？

Answer 1

改用COALESCE:

SELECT
    e.id,
    c.name,
    e.location,
    e.designation,
    e.time_period_from,
    e.time_period_to,
    DATEDIFF(COALESCE(e.time_period_to, NOW()), e.time_period_from) AS tenure_in_days
FROM employment e
LEFT JOIN company c ON (c.id = e.company_id)
LIMIT 0, 10

我猜你想要 DATEDIFF(e.time_period_to, e.time_period_from)。

在没有显式ORDER BY 的情况下使用LIMIT 可能会根据执行计划返回结果。