如何在MySQL中筛选两列日期较大值大于指定日期的数据？

问题背景 #

我有一张名为processes的表，包含以下列：

• id
• date_creation
• date_lastrun

示例数据：

id;date_creation;date_lastrun
1;2022-01-01 00:00:00;2022-02-01 00:00:00
2;2022-03-01 00:00:00;NULL

我可以通过以下语句获取每条记录中日期较大的值：

SELECT id, MAX(IFNULL(date_lastrun, date_creation)) as lastdate 
FROM processes

但尝试筛选lastdate大于指定日期（如2022-03-01）时遇到报错：

1. 使用列别名筛选：

SELECT id, MAX(IFNULL(date_lastrun, date_creation)) as lastdate 
FROM processes 
WHERE DATE(lastdate) > "2022-03-01"

错误信息：#1054 - Unknown column 'lastdate' in 'where clause'

2. 直接在WHERE中使用聚合函数：

SELECT id, MAX(IFNULL(date_lastrun, date_creation)) as lastdate 
FROM processes 
WHERE DATE(MAX(IFNULL(date_lastrun, date_creation))) > "2022-03-01"

错误信息：#1111 - Invalid use of group function

错误原因 #

1. WHERE子句无法识别列别名：SQL执行顺序是先执行WHERE筛选行，再执行SELECT生成列别名，因此WHERE阶段无法读取lastdate这个别名。
2. WHERE子句不能直接使用聚合函数：聚合函数（如MAX()）是对分组后的数据计算，而WHERE是在分组前筛选行，两者执行阶段冲突。

解决方案 #

方案1：使用HAVING子句（分组场景） #

如果需要按id分组后筛选聚合结果，用HAVING子句（它在分组和聚合后执行，可以识别聚合结果或列别名）：

SELECT id, MAX(IFNULL(date_lastrun, date_creation)) as lastdate 
FROM processes
GROUP BY id
HAVING lastdate > '2022-03-01'

注：如果不加GROUP BY id，会将整个表作为一个分组返回单条聚合结果；若要获取每条符合条件的独立记录，必须添加GROUP BY id。

方案2：直接在WHERE中计算比较值 #

如果只是要筛选单条记录中较大日期符合条件的数据，无需聚合函数，直接在WHERE中计算判断：

SELECT id, IFNULL(date_lastrun, date_creation) as lastdate 
FROM processes
WHERE IFNULL(date_lastrun, date_creation) > '2022-03-01'

此方案效率更高，直接对每行数据做判断，无需分组聚合。

方案3：子查询/CTE预计算lastdate #

针对复杂逻辑，可以先通过子查询或CTE生成包含lastdate的临时结果，再筛选：

-- 子查询方式
SELECT id, lastdate
FROM (
    SELECT id, IFNULL(date_lastrun, date_creation) as lastdate 
    FROM processes
) AS temp
WHERE lastdate > '2022-03-01'

-- CTE方式（MySQL 8.0及以上版本支持）
WITH temp AS (
    SELECT id, IFNULL(date_lastrun, date_creation) as lastdate 
    FROM processes
)
SELECT id, lastdate
FROM temp
WHERE lastdate > '2022-03-01'

内容的提问来源于stack exchange，提问作者vegmaster