如何查询数据表中多个给定日期对应的最近有效非周末日期

原语句问题说明 #

你写的查询条件BW_DATE <='2021-09-30' or BW_DATE<='2021-09-26'实际等价于BW_DATE <='2021-09-30'，会返回所有小于等于最大目标日期的记录，没有针对每个目标日期单独计算最新有效日期，所以不符合需求。

正确实现方案 #

核心逻辑是先把所有目标日期单独列出来，逐个计算每个目标日期对应的最大有效日期，最后对结果去重即可，通用写法适配绝大多数SQL引擎：

SELECT DISTINCT latest_valid_date
FROM (
    -- 内层对每个目标日期单独计算最新有效日期
    SELECT MAX(BW_DATE) AS latest_valid_date
    FROM backdated_weekly
    -- 此处维护所有待查询的目标日期，新增目标直接加UNION ALL即可
    CROSS JOIN (
        SELECT '2021-09-30' AS target_date
        UNION ALL
        SELECT '2021-09-26' AS target_date
    ) target_dates
    WHERE BW_DATE <= target_date
    GROUP BY target_date
) t

简化写法（支持VALUES语法的引擎可用，如PostgreSQL、MySQL8.0+、Hive、Spark SQL等） #

SELECT DISTINCT latest_valid_date
FROM (
    SELECT MAX(BW_DATE) AS latest_valid_date
    FROM backdated_weekly
    CROSS JOIN (VALUES ('2021-09-30'), ('2021-09-26')) AS target_dates(target_date)
    WHERE BW_DATE <= target_date
    GROUP BY target_date
) t

逻辑验证 #

针对示例中的两个目标日期，内层分组计算后会得到2021-09-30和2021-09-24两个结果，外层去重后就是预期输出。如果后续新增目标日期，只需要在target_dates子查询中添加对应日期即可。

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